Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum cells in table using column and row conditions
Hi, i need to sum the values in a table based on a name match in COLUMN
B and a text match in ROW 4 - say for each occurrence of "Jim Smith" in range B7:B505,when "text" appears in range E4:GC4, SUM all cells which will contain number values- so if:- text1 text2 text3 text1 Jim Smith 3 6 2 4 Sue Brown 1 5 1 7 Mark Bosman 2 9 3 6 Jim Smith 5 4 2 3 the result would have to be '15'. There are many columns and rows so it must be efficient. I have tried various nested statements and SUMIF using OFFSET but must be able to specify the conditions in context of whole table and I cannot figure this out - looks like a long night ahead! thank you in advance, nelly |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum cells in table using column and row conditions
Aladin Akyurek wrote: In waht follows, A2:A5 houses the names data, B1:E1 the text data, and B2:E5 the figures. =SUM(IF($A$2:$A$5="Jim Smith",IF($B$1:$E$1="text1",$B$2:$E$5))) which needs to be confirmed with control+shift+enter, not with enter. Hi, thanks a lot - this works great. I'm just learning excel formulae/functions. Didn't know about the ctrl/shift/enter thing to accept. Got this just in the nick of time - very much appreciated. nelly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|