"ProductIfs" Like Function (Instead of "SUMIFS")

**clueless12** · 09-07-2015, 01:57 PM

Hi guys,
I know this question has been asked before, but I need a bit more help and explanation. What I really need is a SumIfs-like function that calculates the product of a column, only if certain multiple criteria are met. I have three columns of data; a Category (A-D), a State (many of them, this is just a sample I made up, so I just used WA and WV as an example, but I have way more data than this and multiple states, which is why I don't simply sort the data), and a Percentage Change:
Product Data.xls

What I would like to do is multiply together the % Changes for each of the Categories A-D for WA and VA separately, to get:
Product Results.xls

So I have 2 columns that contain "criteria" (two states and four categories) that need to be met in order for the multiplication to occur. I've tried combinations of the PRODUCT function in Excel along with the IF function and the AND function, but I can't seem to get the right combination, especially because the two criteria must be searched in a range, and the PRODUCT function must be pointed to a range of data as well. If I had a ProductIfs function, this is what I would want:
=PRODUCTIFS(C2:C17,A2:A17,"A",B2:B17,"WA") to get the results for category A for WA.

I've tried using an IF inside of a product function:
=PRODUCT(IF(AND(A2:A17 = "A", B2:B17 = "WA"), C2:C17, 1))
but that doesn't seem to work. Anyone have any other ideas? Or any idea why the above function doesn't work properly?

Thanks in advance for your help!

**zbor** · 09-07-2015, 02:04 PM

Those a very pritty pictures.
Unfortunally, we are excel solvers. Not artists.
Please upload example workbook rather that picture so we don't need to waste time on retyping your data.

Here is one solution with Pivot table.

**clueless12** · 09-07-2015, 02:21 PM

Sorry about that! Wasn't thinking when I uploaded a picture instead of the workbook. I have edited the post.

Thanks for your reply! I think a pivot table would probably be the simplest solution now that you mention it (unless others have other ideas). Thanks so much for the help (and again, sorry for making you recreate the data! Total brain-lapse on my part!!)

**zbor** · 09-07-2015, 02:24 PM

No problem clueless12.
Hopefully you'll have many more questions so I'm guiding you in right direction for a future

PT is simplest in your case and give you fast possibility of filtering.

**clueless12** · 09-07-2015, 02:31 PM

I'm sure I'll have many more questions as I go along and learn to use Excel more efficiently

This forum has been great in giving great solutions and great guidance in the few times I've used it. I'll definitely be back anytime I need help/clarification on an issue!

Thanks again for your help!

You guys are great!!

**FlameRetired** · 09-08-2015, 03:13 AM

You have trailing spaces in both category columns. Once those are cleaned up this works at my end. BTW I copied the table from the second file and pasted it to a second sheet in the first file. The formula in the summary table array-entered in C2 and filled down is

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

The workbook is attached.

Is this what you were looking for?