Thread: Sumproduct

Morning all,

I have the following sumproduct formula that's providing solid results but I would like to add a 3rd column of criteria. I'v tired with little succes.

The following formula <=SUMPRODUCT(('IW 38 DUMP for Planning'!$A$1:$A$10000="2A")*('IW 38 DUMP for Planning'!$E1:$E10000={"PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"}))> totals all of the work in plant area "2A", in this case 52 records. I would like it to filter further with values in $H1:$H1000 matching criteria "CONTRACT", "MACH" OR "HTSMET".

The data is easy to find with pivot tables but I would like to take that manual step out of the reporting being doen from these records.

Thanks

tRY:

Code:

=SUMPRODUCT(('IW 38 DUMP for Planning'!$A$1:$A$10000="2A")*(ISNUMBER(MATCH('IW 38 DUMP for Planning'!$E1:$E10000,{"PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"},0)))*(ISNUMBER(MATCH('IW 38 DUMP for Planning'!$H1:$H10000,{"CONTRACT","MACH","HTSMET"},0))))

Isnumber() is not looking for text values, it is looking at the result of the MATCH() functions.. which returns numbers (position number) of matches found or #N/A if matches not found... so the check is actually if Matches are found then return position number, and then if the postion number exists (isnumber), then return TRUE