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

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.

NEIL@NEIL1975.PLUS.COM wrote:
> 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 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