SUMPRODUCT problem

lmcwrod

New Member
Joined
Jul 13, 2006
Messages
41
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
AZBABBBCBD
2Column1Column2Column3Column4
3Criteria1ABCD
4Criteria2345ANYVALUE
5Rowshowingsumproductvalues#REF!#REF!#REF!#REF!
MaxPumpCapacity
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

New Member
Joined
Jul 13, 2006
Messages
41
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

New Member
Joined
Jul 13, 2006
Messages
41
Building on the Countif problem, I can simply put an if with 2 sumproduct formulas:
=IF(criteria2=0,sumproduct1,sumproduct2)
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
That is exactly what I was going to say.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,881
Messages
5,543,008
Members
410,583
Latest member
gazz57
Top