=SUMPRODUCT formula problems

[Nate Westcott]

I'm trying to write an array formula which searches a column on one sheet for a value in a cell of another sheet, and provides a sum total of the results. The search values are picked from combo boxes.

So far, I have this:

Please Login or Register  to view this content.

This formula is not working, and I have a feeling I am missing something important; can anyone see what I'm doing wrong just by looking at what I have so far? I have attached a sample workbook to show what I am trying to do.

My many thanks for any help anyone can offer!
Nate

[mcmahobt]

By inspection, it is not too clear what you are even trying to do with this formula based on your example. Try this formula in C12 on the example workbook you posted. Is this the expected result?

Please Login or Register  to view this content.

[MarvinP]

Hi Nate,

I'd suggest you use a Pivot Table instead of trying to build these formulas. I also think you want a CountIFS () instead of SumProduct. See the attached.

[Nate Westcott]

By inspection, it is not too clear what you are even trying to do with this formula based on your example.

My apologies, I had entered the formula from my actual workbook, and then attached a sample which was different.

Try this formula in C12 on the example workbook you posted. Is this the expected result?

Please Login or Register  to view this content.

This definitely does what I am trying to do. I noticed that if I adjust the formula to try to locate the index in a range of columns, it stops working. So, for instance:

Please Login or Register  to view this content.

...does not work, because the second condition is now searching D1 through E50; the formula returns a VALUE error. For clarity, I would like to search for the index value in either column D or E in the example above.

Is there a way to successfully search on two columns using this formula? This should be an "OR" statement, not an "AND". It's puzzling, because I thought I had it working yesterday to search on two columns, but now I cannot figure what is incorrect.

Thank you for your help with this!

[mcmahobt]

I'm confused then as to what the need for dropdown selections are. Are you trying to have the table show only values that are associated with the dropdown selections? Or do you want all the values shown on the table simultaneously?

I attached a workbook that takes a stab at it. SUMPRODUCT can work in this instance, but if your actual database is larger, COUNTIFS is better at handling big data.

[Nate Westcott]

Thank you! This definitely gets closer to what I'm looking for.

The idea of the dropdowns is to delimit the results in the table by parameters that aren't shown in the table. So, for example, if I set the 'Season' to Winter 2015, and Quality to 'Good', the table below shows the totals for how many oranges, apples, and pears that fit those parameters were sold at farmstands, and also how many were sold in produce isles. (It's worth noting that my actual workbook has dozens of parameter drop-downs!)

Your formula definitely does the trick!

One further adjustment: how would I alter the formula to ignore a blank cell in the delimiting drop-downs? ( For example, if I want to only see the table set for 'Winter 2015', but the 'Quality' delimiter doesn't matter.) I really appreciate the help with these questions!

[mcmahobt]

To ignore blanks in dropdowns, your source data must not have blanks. If you're not hard coding in the values directly (mainly because of several choices), many people opt to creating a separate worksheet dedicated to dropdown settings that you can then hide. Those, plus VBA, are some options.