SUMIFS needed

[RodneyC]

I'm trying to look on my Revenue Analysis tab for two criteria:

1. First criteria - does it find the value in $D$25 on the Revenue Analysis tab in the range $C2$1185. (It will find this multiple times.)
2. Second criteria, does it find the value in $N$1 on the revenue tab in the range $T$2:$T$1185. If/wherever it finds it, then SUM those values and divide by the value in $M$1
3. Second Criteria continued - If instead of the value in $N$1 it finds the value in $N$2 in the range $T$2:$T$1185 SUM those values and divide by the value in $M$2
4. Second Criteria continued - If instead of the value in $N$1 it finds the value in $N$3 in the range $T$2:$T$1185 SUM those values and divide by the value in $M$3
5. Second Criteria continued - If instead of the value in $N$1 it finds the value in $N$4 in the range $T$2:$T$1185 SUM those values and divide by the value in $M$4

I've started here, but have become lost.

=SUMIFS('Revenue Analysis'!C3:C1185,$D$25,'Revenue Analysis'!T2:T1185,N1) "too many arguments"

Thanks in advance

 

[RasGhul]

Hi Rodney,

Is this question related or the same as your previous 1 from yesterday?

It will help us immensely if you can post some sample data, this way we don't need to create fictional data that might not properly represent your sheet.

This may also lead us to a simpler solution.

Post your Sample data with XL2BB

 

[RodneyC]

It's a result of the question from yesterday. I solved that problem by simply pulling data from each column into a new column on the other tab, using a forth column to sum then divide, then hiding what I wanted hidden. Easier solution than I thought was needed. I

Regarding this issue, I'll dummy up some "non-sensitive" data and post here later.

 

[RodneyC]

I hope this doesn't make it more confusing, but here goes:

Lets say someone selected “Purple” from the Drop Down Menu . Note in column B next to Purple is the value 2. Likewise with Red, Black etc. If they had selected Blue or Yellow, etc. the value would be 3 and so on. These values in column B can be referenced in N1:N4

I need a formula in Recap E25 that will ask these questions and report this result:

1. What value is selected in Recap D4. In this case, it is Purple.
2. What value is selected in Recap D25. In this case it is Walrus.
3. Look on the Revenue Analysis $C$2:$C$1185 for the value Walrus AND where the value in $T$2:$T$1185 = the value associated with the selected value in D4, in this case Purple which produces 2 in column T. It doesn’t show on this screen shot, but there are multiple places where the value in column $T$2:$T$1185 is 2. If a user selected Blue, column T would produce 3,
4. For formula purposes, instead of referring to “2” I’d prefer to use $N$2 as shown on the Recap tab. This will allow me to change that with one entry if ever needed.
5. SUM all of the places where these criteria are met.
6. Now that the formula has calculated this SUM, divide this by the value in M2, in this case 5. This represents the number of “colors” that meet the N2 qualification and is used to get my average.

The formula needs to be smart enough to produce the resulting average regardless of what they select in the drop down menu, using the correct M1:M4 value based on the corresponding value in N1:N4 pulled from column T on the Revenue Analysis tab.

I’ll need to change the formula so that it can be used down the line for D26, D27 and so on.

Thank you

 

[RasGhul]

I can't quite make out the dates on Rev Analysis, I assume they are daily sales samples

On Recap we can sum revenue by Color & Product for between each month e.g Dec/Nov/Oct like in my sample

Hopefully I'm close by returning from RA 3 for Blue which then gets Size 4 to divide Month sales by Avg Size?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O					T
1	Market	Name	1/12/2021	2/12/2021	3/12/2021	1/11/2021	2/11/2021	3/11/2021	4/11/2021	1/10/2021	2/10/2021	3/10/2021	4/10/2021	5/10/2021					Color
2	Purple	Walrus																	2
3	Purple	Hippo	30	20															2
4	Purple	Ape	200																2
5	Purple	Lion																	2
6	Purple	Tiger																	2
7	Blue	Walrus	10	20	30	15	17	18	20	25	29	39	24	17					3
8	Blue	Hippo	10	20	30	15	17	18	20	25	29	39	24	17					3
9	Blue	Ape	10	20	30	15	17	18	20	25	29	39	24	17					3
10	Blue	Lion	10	20	30	15	17	18	20	25	29	39	24	17					3
11	Blue	Tiger	10	20	30	15	17	18	20	25	29	39	24	17					3
12	Green	Walrus	11	12	13	14	15	16	17	18	19	20	21	22					4
13	Green	Hippo	11	12	13	14	15	16	17	18	19	20	21	22					4
14	Green	Ape	11	12	13	14	15	16	17	18	19	20	21	22					4
15	Green	Lion	11	12	13	14	15	16	17	18	19	20	21	22					4
16	Green	Tiger	11	12	13	14	15	16	17	18	19	20	21	22					4
Revenue Analysis

Book1
	A	B	C	D	E	F	G	H	I			L	M	N
1												Sizes	3	1
2												Sizes	5	2
3												Sizes	4	3
4	Everything			Blue	Favorite							Sizes	2	4
5	Purple
6	Blue		Color	Sales	1/12/2021	1/11/2021	1/10/2021	MOM $	MOM %
7	Green		3	Walrus	60	70	134
8			3	Hippo	60	70	134
9			3	Ape	60	70	134
10			3	Lion	60	70	134
11			3	Tiger	60	70	134
12
13		AVG Factor	Color	Average
14		4	3	Walrus	15	17.5	33.5
15		4	3	Hippo	15	17.5	33.5
16		4	3	Ape	15	17.5	33.5
17		4	3	Lion	15	17.5	33.5
18		4	3	Tiger	15	17.5	33.5
Recap

Cell Formulas
Range	Formula
E7:G11	E7	=SUMPRODUCT(('Revenue Analysis'!$B$2:$B$16=Recap!$D$4)*('Revenue Analysis'!$C$2:$C$16=Recap!$D7)*('Revenue Analysis'!$D$1:$O$1>=Recap!E$6)*('Revenue Analysis'!$D$1:$O$1<=EOMONTH(Recap!E$6,0))*'Revenue Analysis'!$D$2:$O$16)
C7:C11,C14:C18	C7	=INDEX('Revenue Analysis'!$T$2:$T$16,MATCH(Recap!$D$4,'Revenue Analysis'!$B$2:$B$16,0))
B14:B18	B14	=INDEX($M$1:$M$4,MATCH(C14,$N$1:$N$4,0))
E14:G18	E14	=SUMPRODUCT(('Revenue Analysis'!$B$2:$B$16=Recap!$D$4)*('Revenue Analysis'!$C$2:$C$16=Recap!$D7)*('Revenue Analysis'!$D$1:$O$1>=Recap!E$6)*('Revenue Analysis'!$D$1:$O$1<=EOMONTH(Recap!E$6,0))*'Revenue Analysis'!$D$2:$O$16)/$B14

Cells with Data Validation
Cell	Allow	Criteria
D4	List	=$A$4:$A$7

 

[RodneyC]

I can't quite make out the dates on Rev Analysis, I assume they are daily sales samples

On Recap we can sum revenue by Color & Product for between each month e.g Dec/Nov/Oct like in my sample

Hopefully I'm close by returning from RA 3 for Blue which then gets Size 4 to divide Month sales by Avg Size?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O					T
1	Market	Name	1/12/2021	2/12/2021	3/12/2021	1/11/2021	2/11/2021	3/11/2021	4/11/2021	1/10/2021	2/10/2021	3/10/2021	4/10/2021	5/10/2021					Color
2	Purple	Walrus																	2
3	Purple	Hippo	30	20															2
4	Purple	Ape	200																2
5	Purple	Lion																	2
6	Purple	Tiger																	2
7	Blue	Walrus	10	20	30	15	17	18	20	25	29	39	24	17					3
8	Blue	Hippo	10	20	30	15	17	18	20	25	29	39	24	17					3
9	Blue	Ape	10	20	30	15	17	18	20	25	29	39	24	17					3
10	Blue	Lion	10	20	30	15	17	18	20	25	29	39	24	17					3
11	Blue	Tiger	10	20	30	15	17	18	20	25	29	39	24	17					3
12	Green	Walrus	11	12	13	14	15	16	17	18	19	20	21	22					4
13	Green	Hippo	11	12	13	14	15	16	17	18	19	20	21	22					4
14	Green	Ape	11	12	13	14	15	16	17	18	19	20	21	22					4
15	Green	Lion	11	12	13	14	15	16	17	18	19	20	21	22					4
16	Green	Tiger	11	12	13	14	15	16	17	18	19	20	21	22					4
Revenue Analysis

Book1
	A	B	C	D	E	F	G	H	I			L	M	N
1												Sizes	3	1
2												Sizes	5	2
3												Sizes	4	3
4	Everything			Blue	Favorite							Sizes	2	4
5	Purple
6	Blue		Color	Sales	1/12/2021	1/11/2021	1/10/2021	MOM $	MOM %
7	Green		3	Walrus	60	70	134
8			3	Hippo	60	70	134
9			3	Ape	60	70	134
10			3	Lion	60	70	134
11			3	Tiger	60	70	134
12
13		AVG Factor	Color	Average
14		4	3	Walrus	15	17.5	33.5
15		4	3	Hippo	15	17.5	33.5
16		4	3	Ape	15	17.5	33.5
17		4	3	Lion	15	17.5	33.5
18		4	3	Tiger	15	17.5	33.5
Recap

Cell Formulas
Range	Formula
E7:G11	E7	=SUMPRODUCT(('Revenue Analysis'!$B$2:$B$16=Recap!$D$4)*('Revenue Analysis'!$C$2:$C$16=Recap!$D7)*('Revenue Analysis'!$D$1:$O$1>=Recap!E$6)*('Revenue Analysis'!$D$1:$O$1<=EOMONTH(Recap!E$6,0))*'Revenue Analysis'!$D$2:$O$16)
C7:C11,C14:C18	C7	=INDEX('Revenue Analysis'!$T$2:$T$16,MATCH(Recap!$D$4,'Revenue Analysis'!$B$2:$B$16,0))
B14:B18	B14	=INDEX($M$1:$M$4,MATCH(C14,$N$1:$N$4,0))
E14:G18	E14	=SUMPRODUCT(('Revenue Analysis'!$B$2:$B$16=Recap!$D$4)*('Revenue Analysis'!$C$2:$C$16=Recap!$D7)*('Revenue Analysis'!$D$1:$O$1>=Recap!E$6)*('Revenue Analysis'!$D$1:$O$1<=EOMONTH(Recap!E$6,0))*'Revenue Analysis'!$D$2:$O$16)/$B14

Cells with Data Validation
Cell	Allow	Criteria
D4	List	=$A$4:$A$7

Thanks for the help. My wife is asking "when are you coming downstairs to watch the movie with us?" I'll dive into this tomorrow AM.

 

[RodneyC]

I think there's an easier way to do this, just using SUMIFS. Stay tuned