sum tables cells using row and column conditions

[nelly]

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

 

[Bob Phillips]

=SUMPRODUCT((A7:A505="Jim Smith")*(B4:GC4="text1")*(B7:GC505))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[nelly]

=SUMPRODUCT((A7:A505="Jim Smith")*(B4:GC4="text1")*(B7:GC505))

Thanks a lot - I aso found that this works:-

=SUM(IF(A7:A505="Jim Smith",IF(B4:GC4="text1",B7:GC505)))

MUST press ctrl/shift/enter to accept in cell - not just enter

many thanks for solution

 

[Bob Phillips]

It does, but as you discovered, it is an array formula. SP is not, so is
easier to maintain.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)