SUMIFS Not Working?

[canthony24]

I'm trying to create a sumifs formula to determine the sales of a state for a certain sku in a timeframe. This is just a subset of the source data. I'll be looking for a few states, but Iowa to start.

This info will be populated in another cell.

Iowa for sku 2330993 and for this week 201711.


[TABLE="width: 470"]
<tbody>[TR]
[TD]A
State[/TD]
[TD]B
SKU[/TD]
[TD]C
Calendar Year Week[/TD]
[TD]D
Cases[/TD]
[/TR]
[TR]
[TD]IOWA[/TD]
[TD]2330993[/TD]
[TD="align: right"]201705[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]IOWA[/TD]
[TD]2330993[/TD]
[TD="align: right"]201712[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]IOWA[/TD]
[TD]2330993[/TD]
[TD="align: right"]201714[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]ALABAMA[/TD]
[TD]2330993[/TD]
[TD="align: right"]201705[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]ALABAMA[/TD]
[TD]2330993[/TD]
[TD="align: right"]201711[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]ALABAMA[/TD]
[TD]2330993[/TD]
[TD="align: right"]201718[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]NORTH CAROLINA[/TD]
[TD]2330993[/TD]
[TD="align: right"]201712[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]NORTH CAROLINA[/TD]
[TD]2752063[/TD]
[TD="align: right"]201712[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]NORTH CAROLINA[/TD]
[TD]4561413[/TD]
[TD="align: right"]201719[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ARIZONA[/TD]
[TD]2752063[/TD]
[TD="align: right"]201713[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]ARIZONA[/TD]
[TD]2752063[/TD]
[TD="align: right"]201714[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

=SUMPRODUCT((A1:A11="IOWA")*(B1:B11=2330993)*(C1:C11=201711)*(D1:D11))
or
=SUMIFS(D1:D11,A1:A11,"IOWA",B1:B11,2330993,C1:C11,201711)

NOTE: Your example data returns 0 since there is no Calendar week 201711 for IOWA in your example data.

 

[canthony24]

Ah okay, thanks!!