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?

	A	B	C	D	E	F	G	H	I
1	Year	Week	Shop	Beer Sales	Wine Sales	Spirit Sales
2	2017	1	West	500	100	300
3	2017	1	North	200	400	500
4	2017	1	South	100	500	300
5	2017	2	West	200	500	350
6	2018	1	North	400	200	500
7	2018	2	West	200	200	600
8
9

<tbody>
</tbody>

 

[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.