sum cells in table using column and row conditions

[NEIL]

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

 

[Aladin Akyurek]

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.

 

[Nelly]

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