SOLVED
Home

Can I give a cell a hidden value and then add hidden value up?

%3CLINGO-SUB%20id%3D%22lingo-sub-879035%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20give%20a%20cell%20a%20hidden%20value%20and%20then%20add%20hidden%20value%20up%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416273%22%20target%3D%22_blank%22%3E%40natjo8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20not%20upload%20a%20small%20sample%20file%20with%20some%20dummy%20data%20(not%20an%20image%20of%20data)%20along%20with%20desired%20output%20mocked%20up%20manually%20so%20that%20it%20would%20be%20easy%20to%20visualize%20what%20exactly%20you%20are%20trying%20to%20achieve.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878993%22%20slang%3D%22en-US%22%3ECan%20I%20give%20a%20cell%20a%20hidden%20value%20and%20then%20add%20hidden%20value%20up%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878993%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20I%20give%20a%20cell%20a%20value%20without%20that%20value%20being%20in%20the%20cell%3F%26nbsp%3B%20%26nbsp%3BI%20need%20to%20work%20out%20TOTAL%20HOURS%2FWEEK%20based%20on%20the%20cells%20with%20'X'%20being%20worth%20a%20certain%20number%20of%20hours%20(either%202.5%20or%201).%26nbsp%3B%20Can%20anyone%20please%20help%20with%20the%20correct%20formula(s)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3E%3CP%3ETOTAL%20HOURS%2F%3C%2FP%3E%3CP%3EWEEK%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESession%3C%2FP%3E%3CP%3EMon%20A.M.%3C%2FP%3E%3CP%3E2.5%20Hours%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESession%3C%2FP%3E%3CP%3EMon%26nbsp%3BP.M.%3C%2FP%3E%3CP%3E1%20Hour%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESession%3C%2FP%3E%3CP%3ETues%20A.M.%3C%2FP%3E%3CP%3E2.5%20Hours%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESession%3C%2FP%3E%3CP%3ETues%20P.M.%3C%2FP%3E%3CP%3E1%20Hour%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESession%3C%2FP%3E%3CP%3EWed%20A.M.%3C%2FP%3E%3CP%3E2.5%20Hours%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESession%3C%2FP%3E%3CP%3EWed%20P.M.%3C%2FP%3E%3CP%3E1%20Hour%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFreddie%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBobby%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMargi%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-878993%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879116%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20give%20a%20cell%20a%20hidden%20value%20and%20then%20add%20hidden%20value%20up%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20the%20advice%2C%20I'm%20new%20to%20this!%26nbsp%3B%20I've%20added%20a%20table%20which%20hopefully%20helps%20explain%20my%20query%20a%20bit%20better!%26nbsp%3B%20Many%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879153%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20give%20a%20cell%20a%20hidden%20value%20and%20then%20add%20hidden%20value%20up%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416273%22%20target%3D%22_blank%22%3E%40natjo8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20insert%20a%20row%20to%20have%20the%20Hours%20as%20shown%20below...%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20897px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133760i28CFAAAF92B8A9E7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Total%20Hours.jpg%22%20title%3D%22Total%20Hours.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20simply%20use%20the%20SUMIF%20formula%20in%20B2%20and%20copy%20it%20down.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMIF(C3%3AH3%2C%22x%22%2C%24C%242%3A%24H%242)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879177%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20give%20a%20cell%20a%20hidden%20value%20and%20then%20add%20hidden%20value%20up%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879177%22%20slang%3D%22en-US%22%3EThank%20you!!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879337%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20give%20a%20cell%20a%20hidden%20value%20and%20then%20add%20hidden%20value%20up%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416273%22%20target%3D%22_blank%22%3E%40natjo8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%3C%2FP%3E%3CP%3EIf%20that%20resolves%20your%20question%2C%20please%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20solution%20as%20a%20Best%20Response%2FAnswer%20to%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E
natjo8
New Contributor

Can I give a cell a value without that value being in the cell?   I need to work out TOTAL HOURS/WEEK based on the cells with 'X' being worth a certain number of hours (either 2.5 or 1).  Can anyone please help with the correct formula(s)?

 

Name

TOTAL HOURS/

WEEK

Session

Mon A.M.

2.5 Hours

Session

Mon P.M.

1 Hour

Session

Tues A.M.

2.5 Hours

Session

Tues P.M.

1 Hour

Session

Wed A.M.

2.5 Hours

Session

Wed P.M.

1 Hour

Freddie x x x 
Bobby xxxxxx
Margi xxx  x
5 Replies

@natjo8 

Why not upload a small sample file with some dummy data (not an image of data) along with desired output mocked up manually so that it would be easy to visualize what exactly you are trying to achieve.

@Subodh_Tiwari_sktneer  Thanks for the advice, I'm new to this!  I've added a table which hopefully helps explain my query a bit better!  Many thanks

Solution

@natjo8 

If you insert a row to have the Hours as shown below...

Total Hours.jpg

 

You can simply use the SUMIF formula in B2 and copy it down.

=SUMIF(C3:H3,"x",$C$2:$H$2)

 

 

@natjo8 

You're welcome!

If that resolves your question, please take a minute to accept the post with the proposed solution as a Best Response/Answer to mark your question as Solved.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies