Thread: SUMIF help, how to use SUMIF with 2D data

I am trying to put together a report of POS data using more complex formulas. Here is a short example:

Segment Wk 1 Wk 2 Wk 3 Wk 4 Wk 5 Wk 6 Wk 7 Wk 8 Wk 9 Wk 10 Wk 11 Wk 12
Car $10,170 $11,177 $12,329 $12,890 $12,985 $14,199 $13,226 $14,707 $14,627 $14,547 $13,721 $15,957
Car $10,793 $11,663 $13,395 $13,207 $13,999 $13,648 $12,759 $14,077 $13,084 $14,849 $14,336 $15,764
Doll $625 $613 $816 $902 $1,053 $979 $970 $1,244 $1,035 $893 $776 $1,195
Doll $413 $471 $690 $798 $767 $653 $807 $825 $810 $847 $829 $939
Coloring $3,581 $3,671 $5,509 $5,390 $5,408 $5,058 $5,130 $6,007 $5,397 $5,184 $4,220 $4,848
Coloring $376 $542 $906 $1,075 $1,060 $915 $893 $859 $585 $561 $450 $474
Coloring $1,405 $1,692 $2,171 $2,485 $2,165 $1,833 $2,024 $2,419 $1,914 $1,818 $1,594 $1,872


I need to get a sum of the last 4 wks, 12 wks, and YTD of each segment. I have all the data on one sheet, and a chart on another, like this:

Latest 4 wks Latest 12 wks YTD
Cars $85,457 $335,401 $814,219
Dolls $69,492 $410,343 $1,020,006
Coloring $116,702 $399,997 $927,207


I am been just manually selecting the data using the SUM function, but I'm sure there's an easier and quicker way to do it, since I have to make this report weekly with updated data.

I've tried using =SUMIF('pos data'!B2:b8,a3,'pos data'!C2:c3)+SUMIF('pos data'!B2:b8,a3,'pos data'!D2:D3)+SUMIF('pos data'!B2:b8,a3,'pos data'!E2:e3)...and so on for 12 wks, In English, if Data Sheet column B=Cars, add C2 and C3 (wk 1), then if Data Sheet column B=Cars, add d2 and d3 (wk 2), and so on, and then add all those answers together again, which works but gets really lengthy!.

I have also tried =SUMIF('pos data'!B2:b8,a3,'pos data'!C2:N3), which will only add the first column (C2 + C3) together, not the whole 2D section.

What should I do to fix this? Maybe an array, but I can't figure out how to incorporate this.

Thanks!

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!