Suming selected cells based on two criteria

[Guest]

I am wishing to search two specific columns (text) in a data base for two
separate specific criteria (text)and if these are a match then I want to sum
the numbers in a third colum that match the two criteria.
It would be like a dual VLOOKUP with a SUMIF attached based on the basis of
the results of a TRUE outcome of the VLOOKUP.
Does anyone know if it is possible to search on two criteria and if a match
sum all items in a third column, that have a match in regards to the two
search criteria.

You will be my idol if you are able to solve this one.

 

[Guest]

SUMPRODUCT would be your idol here <g>

Try something along these lines ..
In say, F1:
=SUMPRODUCT((A2:A100="Text1")*(B2:B100="Text2"),C2:C100)
would sum col C where col A = "Text1" and col B = "Text2"
Note that the 3 ranges need to be identically sized, and SUMPRODUCT dosen't
accept entire col refs eg: A:A, B:B, C:C

And instead of hardcoding the criteria in the formula, perhaps better to
point to cells housing criteria, eg we could have it in F1 as:
=SUMPRODUCT(($A$2:$A$100=D1)*($B$2:$B$100=E1),$C$2:$C$100)
where D1, E1 houses the criteria: Text1, Text2
With the 3 ranges fixed with the dollar signs, F1 could then be copied down
to return correspondingly for other sets of criteria in D2:E2, D3:E3, etc

 

[Guest]

Max you are my idol. You are an absolute legend and have provided me with a
much needed solution. Thankyou very much. YEAH!!!!!!!!

 

[Guest]

You're welcome, Gerard!
Thanks for the spirited call-back <g>