Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!!!!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting cells based on conditional formatting | Excel Discussion (Misc queries) | |||
how do i add in numbers automatically based on adjacent cells cont | Excel Discussion (Misc queries) | |||
sum number of cells based on letter/title criteria | Excel Worksheet Functions | |||
Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) |