SUMIF help, how to use SUMIF with 2D data

**Paul** · 07-22-2011, 11:37 AM

Hi Kizatie, welcome to the forum.

SUMPRODUCT should work for you in this case. See the attached workbook for an example, and let us know if you need additional assistance figuring out the formula(s) needed. If you look in the formulas on the Summary tab in row 2, the only thing that changes is the last column reference.

$C$2:$F$8, $C$2:$N$8, $C$2:$AB$8 (I made up that last one since you didn't have weekly data out to column AB.)

Hope that helps!

**kizatie8907** · 07-22-2011, 11:49 AM

I'm sorry, Paul. This does not work. I need to use some sort of IF function, because I need to reference the SEGMENT column. When new products some out, there may be 3 cars instead of 2, or 1 car instead of 2 if a product is discontinued. Plus, I don't need a product. I just need a sum.

Originally Posted by Paul

Hi Kizatie, welcome to the forum.

SUMPRODUCT should work for you in this case. See the attached workbook for an example, and let us know if you need additional assistance figuring out the formula(s) needed. If you look in the formulas on the Summary tab in row 2, the only thing that changes is the last column reference.

$C$2:$F$8, $C$2:$N$8, $C$2:$AB$8 (I made up that last one since you didn't have weekly data out to column AB.)

Hope that helps!

**Paul** · 07-22-2011, 11:53 AM

It doesn't matter how many instances of Car or Doll or Coloring exist, it will only sum up the columns C:?? if column B is the same as the value in your summary table field.

It's not doing a "PRODUCT" in the normal sense of the word, so my guess is you're unfamiliar with the function and how it works. The "PRODUCT" part of this is multiplying by 1 or 0, whether the condition is met or not.

If it's not working for you, upload a copy of your workbook here and note where you want to see the totals and what they should be if you manually calculated them.