Sumifs using more than one axis???? Crazy!!!!

[Mark McInerney]

Hi,

I have the following as an example of the data that I am working with.

I am trying to get a formula that allows me to select the year, week number, and shop, and also allow me to dynamically select the sales (Beer or Wine or Spirits) and then sum them over a period that I define e.g. I want to sum all sales where the year is 2017, the week range is between 1 and 3, the Shop is West, and the category are wine sales only ( I will be selecting all the values from a drop down box).

Is this possible?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Year[/TD]
[TD]Week[/TD]
[TD]Shop[/TD]
[TD]Beer Sales[/TD]
[TD]Wine Sales[/TD]
[TD]Spirit Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2017[/TD]
[TD]1[/TD]
[TD]West[/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2017[/TD]
[TD]1[/TD]
[TD]North[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2017[/TD]
[TD]1[/TD]
[TD]South[/TD]
[TD]100[/TD]
[TD]500[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2017[/TD]
[TD]2[/TD]
[TD]West[/TD]
[TD]200[/TD]
[TD]500[/TD]
[TD]350[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2018[/TD]
[TD]1[/TD]
[TD]North[/TD]
[TD]400[/TD]
[TD]200[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2018[/TD]
[TD]2[/TD]
[TD]West[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Try

=SUMPRODUCT((A2:A7=2017)*(B2:B7<=3)*(C2:C7="West")*(D1:F1="Wine Sales")*(D2:F7))

Change the criteria to ref the drop down cells

 

[gaz_chops]

Like

=SUMPRODUCT((A2:A7=A10)*(B2:B7<=A11)*(C2:C7=A12)*(D1:F1=A13)*(D2:F7))

[TABLE="width: 532"]
<tbody>[TR]
[TD="class: xl63, width: 87"]Year[/TD]
[TD="class: xl63, width: 87"]Week[/TD]
[TD="class: xl63, width: 97"]Shop[/TD]
[TD="class: xl63, width: 87"]Beer Sales[/TD]
[TD="class: xl63, width: 87"]Wine Sales[/TD]
[TD="class: xl63, width: 87"]Spirit Sales[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2017[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]West[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]300[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2017[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]North[/TD]
[TD="class: xl63, align: right"]200[/TD]
[TD="class: xl63, align: right"]400[/TD]
[TD="class: xl63, align: right"]500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2017[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]South[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]300[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2017[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]West[/TD]
[TD="class: xl63, align: right"]200[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]350[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2018[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]North[/TD]
[TD="class: xl63, align: right"]400[/TD]
[TD="class: xl63, align: right"]200[/TD]
[TD="class: xl63, align: right"]500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2018[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]West[/TD]
[TD="class: xl63, align: right"]200[/TD]
[TD="class: xl63, align: right"]200[/TD]
[TD="class: xl63, align: right"]600[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2017[/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wine Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mark McInerney]

Super - TY

 

[gaz_chops]

You're welcome.