SUMPRODUCT problem

[lmcwrod]

Hope somebody can help with the following SUMPRODUCT problem. I have a formula that I want to copy across many cells in a row. The sumproduct formula will match the values in the 2 cells above (criteria 1, criteria 2) with values in a database. However, for some cells, I don't care what value is in criteria 2 (only criteria 1 is relevant for certain cells in this row). Thus, I want it to be able to use a wildcard and have the SUMPRODUCT incorporate the values regardless of what appears for criteria 2 in the database. I am trying to put a '*' in the criteria 2 cell for the cells where I don't care what the criteria 2 value is and have SUMPRODUCT read this wildcard but it's not working out.

Hope the snipit below helps clarify:

PMG_MarketShares_AD_5.xls
	AZ	BA	BB	BC	BD
2		Column1	Column2	Column3	Column4
3	Criteria1	A	B	C	D
4	Criteria2	3	4	5	ANYVALUE
5	Rowshowingsumproductvalues	#REF!	#REF!	#REF!	#REF!
MaxPumpCapacity

 

[NBVC]

Try:

=IF(BA4="ANYVALUE",COUNTIF(database.xls!A1:A200,BA3),SUMPRODUCT(--(databasefile.xls!A1:A200=BA3),--(databasefile.xls!B1:B200=BA4)))

Note: you can replace "ANYVALUE" in the formula with a null, "" if you want to actually leave Criteria 2 blank.

 

[lmcwrod]

Thanks, NBVC. For the purpose of clarity, I think I oversimplified the situation. The SUMPRODUCT formula actually reads 3 criteria. 2 of which are above and 1 of which is the first value in each row. I don't think the COUNTIF will work with 2 criteria.

Do you think there is a way to do this with more 2 criteria that must be matched but 1 criteria that matters for some cells but not for all?

 

[lmcwrod]

Building on the Countif problem, I can simply put an if with 2 sumproduct formulas:
=IF(criteria2=0,sumproduct1,sumproduct2)

 

[NBVC]

That is exactly what I was going to say.