Suming selected cells based on two criteria
 

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.

Suming selected cells based on two criteria
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gerard" wrote:
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.

Suming selected cells based on two criteria
 

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

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gerard" wrote:
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.

Suming selected cells based on two criteria
 

You're welcome, Gerard!
Thanks for the spirited call-back <g
--
Max
Singapore (.. we're 41 today!)
http://savefile.com/projects/236895
xdemechanik
---
"Gerard" wrote:
Max you are my idol. You are an absolute legend and have provided me with a
much needed solution. Thank you very much. YEAH!!!!!!!!