Use reference cells in the formula to create subtotal

[Burrelli]

This is the formula I have in cell H5
=SUMIF('GSC Data 16mo'!C:C, ">=10000", 'GSC Data 16mo'!B:B) - I need the literal to be replace be the subtotal of a filtered cell

C:C are Impressions and B:B are Clicks

What I need to have calculated in cell H5 is the total/sum of the Impressions Column C:C where the filter on B:B (Clicks Column) are greater than say 500 from a control/user input cell S5. So, return the total Impressions when the Total Clicks column is filtered by "x".

In my head...
=SUMIF('GSC Data 16mo'!C:C, [where 'GSC Data 16mo'!B:B, ">"&S5) ]

also need similar a SUMIFS version:

=SUMIFS('GSC Data 16mo'!C:C, 'GSC Data 16mo'!C:C, [where 'GSC Data 16mo'!B:B, ">"&S6) ], 'GSC Data 16mo'!C:C, [where 'GSC Data 16mo'!B:B, "<"&T6) ]

 

[awoohaw]

Can you post your worksheets data ranges as a mini workbook using the xl2bb add in (link below). your text is very small, and the forum users can also work with your actual data instead of trying to recreate it.

 

[Burrelli]

Can you post your worksheets data ranges as a mini workbook using the xl2bb add in (link below). your text is very small, and the forum users can also work with your actual data instead of trying to recreate it.

Traffic Trajectory Calculator Master.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3					YoY Compare
4		Criteria	URL Group	URL Count	URLS Gaining	URLS Declining	URLS No Data Last Year	Aggr 16mo Impr Volume	Aggr 16mo Click Vol	% Index Size	% Click Contribution	% Impression Contribution						Level	> Value	< Value
5		500+ Clicks	Performance	92	4	11	1	334,602.00	270,509	9.20	73.40	12.27						Performanc	500
6		100 - 501 Clicks	Good	302	38	34	2	1,754,220.00	60,831	30.20	16.51	64.33						Good	100	501
7		10 - 101 Clicks	Fair	606	284	208	294	629,402.00	37,178	60.60	10.09	23.08						Fair	10	101
8		0 - 11 Clicks	Weak	0	7	36	81	8,657.00	0	0.00	0.00	0.32						Weak	0	11
9		0 Click > 100 Impressions	Opportunity	0					0	0.00	0.00	0.00
10		0 Click, < 100 Impressions	Dead	0				0.00	0	0.00	0.00	0.00
Summary YoY

Cell Formulas
Range	Formula
E5	E5	=COUNTIFS('L3M YoY'!B:B,">"&S5,'L3M YoY'!M:M,"*UPWARDS*")
F5	F5	=COUNTIFS('L3M YoY'!B:B,">="&S5,'L3M YoY'!M:M,"*DOWNWARDS*")
G5	G5	=COUNTIFS('L3M YoY'!B:B,">="&S5,'L3M YoY'!M:M,"")
H5	H5	=SUMIF('GSC Data 16mo'!C:C, ">=10000", 'GSC Data 16mo'!B:B)
E6:E8	E6	=COUNTIFS('L3M YoY'!B:B,">"&S6, 'L3M YoY'!B:B,"<"&T6, 'L3M YoY'!M:M,"*UPWARDS*")
F6:F8	F6	=COUNTIFS('L3M YoY'!B:B,">"&S6, 'L3M YoY'!B:B,"<"&T6, 'L3M YoY'!M:M,"*DOWNWARDS*")
G6:G8	G6	=COUNTIFS('L3M YoY'!B:B,">"&S6,'L3M YoY'!B:B,"<"&T6,'L3M YoY'!M:M,"")
H6	H6	=SUMIFS('GSC Data 16mo'!C:C, 'GSC Data 16mo'!C:C, ">=5001", 'GSC Data 16mo'!C:C, "<=10000")
H7	H7	=SUMIFS('GSC Data 16mo'!C:C, 'GSC Data 16mo'!C:C, ">=1001", 'GSC Data 16mo'!C:C, "<=5000")
H8	H8	=SUMIFS('GSC Data 16mo'!C:C, 'GSC Data 16mo'!C:C, ">=101", 'GSC Data 16mo'!C:C, "<=1000")
I5	I5	=SUMIF('GSC Data 16mo'!B:B, ">"&S5, 'GSC Data 16mo'!B:B)
J5:J10	J5	=D5/$C$17*100
K5:K10	K5	=I5/$C$18*100
L5:L10	L5	=H5/$C$19*100
I6:I8	I6	=SUMIFS('GSC Data 16mo'!B:B, 'GSC Data 16mo'!B:B, ">"&S6, 'GSC Data 16mo'!B:B, "<"&T6)
I9:I10	I9	=SUMIF('GSC Data 16mo'!B:B, "=0", 'GSC Data 16mo'!B:B)
B5	B5	=CONCAT(S5, "+ Clicks")
B6:B8	B6	=CONCATENATE(S6," - ",T6, " Clicks")
D5	D5	=COUNTIF('GSC Data 16mo'!B:B, ">"&S5)
D6:D8	D6	=COUNTIFS('GSC Data 16mo'!B:B, ">"&S6, 'GSC Data 16mo'!B:B, "<"&T6)
D9	D9	=COUNTIFS('GSC Data 16mo'!B:B, "<1", 'GSC Data 16mo'!C:C, ">100")
D10	D10	=COUNTIFS('GSC Data 16mo'!B:B, "<1", 'GSC Data 16mo'!C:C, "<100")
H10	H10	=SUMIF('GSC Data 16mo'!C:C, "<=100", 'GSC Data 16mo'!B:B)

 

[Burrelli]

New at this part... I think I got it but let me know. Thanks!

 

[awoohaw]

Thanks for the mini. It helps! Welcome to the forum!

YOu have a lot of formulas referencing other sheets. but it is the formulas and your row/column infor i like to see. Sometimes the accurate data helps. but this is a start. If you want to post the infor from the other worksheets, it may help even more.

 

[Burrelli]

Traffic Trajectory Calculator Master.xlsx
	A	B	C	D	E
1	Top pages	Clicks	Impressions	CTR	Position
2	Shop Statues - Sculptures - Garden & Home Decor - Design Toscano	107312	1421441	7.55%	20.37
3	Shop Our Garden Statues - Design Toscano	10726	1037695	1.03%	10.67
4	Garden Statues & Décor On Sale - Design Toscano	10046	1039636	0.97%	10.84
5	Garden Gnomes - Yard Gnomes & Statues - Design Toscano	6503	2075151	0.31%	9.8
6	Gnomes - Garden Gnomes & Statues - Yard Gnomes - Design Toscano	6341	2499348	0.25%	9.56
7	Dinosaur Statues - T-Rex & Raptors - Design Toscano	5945	264466	2.25%	9.49
8	Medieval & Gothic Furniture - Design Toscano	4128	213489	1.93%	10.41
9	https://www.designtoscano.com/categories/gargoyle-statues	4026	806378	0.50%	10.77
10	https://www.designtoscano.com/categories/asian-zen-garden-statues	3820	357758	1.07%	13.46
11	https://www.designtoscano.com/categories/egyptian	3799	246678	1.54%	10.24
12	https://www.designtoscano.com/categories/large-outdoor-christmas-decoration	3734	475887	0.78%	31.46
13	https://www.designtoscano.com/catalog_forms/new/	3713	710899	0.52%	7.32
14	https://www.designtoscano.com/categories/nude-statues-sculptures	3590	145028	2.48%	7.15
15	https://www.designtoscano.com/categories/animal-statues	3558	430714	0.83%	9.56
16	https://www.designtoscano.com/categories/grand-scale-animal-statues	3393	158258	2.14%	11.23
17	https://www.designtoscano.com/categories/home-decor	3030	666285	0.45%	3.3
18	https://www.designtoscano.com/categories/bear-garden-statues	2916	222286	1.31%	9.08
19	https://www.designtoscano.com/categories/furniture	2837	560842	0.51%	8.84
20	https://www.designtoscano.com/categories/sale	2787	704052	0.40%	3.25
21	https://www.designtoscano.com/categories/angel-cherub-statues	2753	455898	0.60%	9.34
22	https://www.designtoscano.com/categories/garden-fountains	2425	716411	0.34%	25.73
23	https://www.designtoscano.com/categories/greek-roman-statues	2385	240778	0.99%	18.33
24	https://www.designtoscano.com/categories/decorative-walking-sticks	2375	287343	0.83%	17.65
25	https://www.designtoscano.com/categories/gargoyle-dragon-statues	2241	147419	1.52%	10.78
26	https://www.designtoscano.com/categories/egyptian-furniture	2131	74804	2.85%	12.9
27	https://www.designtoscano.com/categories/fairy-outdoor-statues	2058	215527	0.95%	11.75
28	https://www.designtoscano.com/categories/stained-glass-windows	1967	560422	0.35%	21.37
29	https://www.designtoscano.com/products/life-size-cigar-store-indian-statue-1-ne180055	1904	155190	1.23%	7.15
30	https://www.designtoscano.com/categories/bronze-statues-for-the-garden	1785	201717	0.88%	20.11
GSC Data 16mo

 

[awoohaw]

I think this is the worksheet I need to work with. If I get a formula from this you can move it to the report worksheet. Is that right?
But, to summarize your requirement:
you want get a a total of the cells in Column C only where the adjacent cell in B is between the values in S and T on the report sheet?

 

[awoohaw]

Okay, Here is a stab at it:

I had to change some click values to get impression counts:

Book1
	B	C	D	E	F	G	H	I	J
1	Clicks	Impressions	CTR	Position			Click Ranges:		Impressions
2	107312	1421441	0.0755	20.37		Performanc	500		12,549,111
3	10726	1037695	0.0103	10.67		Good	100	501	3,625,360
4	10046	1039636	0.0097	10.84		Fair	10	101	715,612
5	6503	2075151	0.0031	9.8		Weak	0	11	201,717
6	6341	2499348	0.0025	9.56
7	5945	264466	0.0225	9.49
8	4128	213489	0.0193	10.41
9	4026	806378	0.005	10.77
10	3820	357758	0.0107	13.46
11	3799	246678	0.0154	10.24
12	3734	475887	0.0078	31.46
13	3713	710899	0.0052	7.32
14	3590	145028	0.0248	7.15
15	3558	430714	0.0083	9.56
16	3393	158258	0.0214	11.23
17	3030	666285	0.0045	3.3
18	215	222286	0.0131	9.08
19	358	560842	0.0051	8.84
20	258	704052	0.004	3.25
21	215	455898	0.006	9.34
22	280	716411	0.0034	25.73
23	339	240778	0.0099	18.33
24	216	287343	0.0083	17.65
25	440	147419	0.0152	10.78
26	219	74804	0.0285	12.9
27	351	215527	0.0095	11.75
28	90	560422	0.0035	21.37
29	50	155190	0.0123	7.15
30	5	201717	0.0088	20.11
31
GSC Data 16mo

Cell Formulas
Range	Formula
J2	J2	=SUM((--($B$2:$B$30>$H$2))*($C$2:$C$30))
J3:J5	J3	=SUM((--($B$2:$B$30>$H3))*(--($B$2:$B$30)<$I3)*($C$2:$C$30))

 

[awoohaw]

Okay, Here is a stab at it:

I had to change some click values to get impression counts:

Book1
	B	C	D	E	F	G	H	I	J
1	Clicks	Impressions	CTR	Position			Click Ranges:		Impressions
2	107312	1421441	0.0755	20.37		Performanc	500		12,549,111
3	10726	1037695	0.0103	10.67		Good	100	501	3,625,360
4	10046	1039636	0.0097	10.84		Fair	10	101	715,612
5	6503	2075151	0.0031	9.8		Weak	0	11	201,717
6	6341	2499348	0.0025	9.56
7	5945	264466	0.0225	9.49
8	4128	213489	0.0193	10.41
9	4026	806378	0.005	10.77
10	3820	357758	0.0107	13.46
11	3799	246678	0.0154	10.24
12	3734	475887	0.0078	31.46
13	3713	710899	0.0052	7.32
14	3590	145028	0.0248	7.15
15	3558	430714	0.0083	9.56
16	3393	158258	0.0214	11.23
17	3030	666285	0.0045	3.3
18	215	222286	0.0131	9.08
19	358	560842	0.0051	8.84
20	258	704052	0.004	3.25
21	215	455898	0.006	9.34
22	280	716411	0.0034	25.73
23	339	240778	0.0099	18.33
24	216	287343	0.0083	17.65
25	440	147419	0.0152	10.78
26	219	74804	0.0285	12.9
27	351	215527	0.0095	11.75
28	90	560422	0.0035	21.37
29	50	155190	0.0123	7.15
30	5	201717	0.0088	20.11
31
GSC Data 16mo

Cell Formulas
Range	Formula
J2	J2	=SUM((--($B$2:$B$30>$H$2))*($C$2:$C$30))
J3:J5	J3	=SUM((--($B$2:$B$30>$H3))*(--($B$2:$B$30)<$I3)*($C$2:$C$30))

you may need to update the >,< signs (add = to one or both) to make the comparisons work correctly.

 

[Burrelli]

Thank you for your help. I believe I got what I am looking for with:
=SUMIFS('GSC Data 16mo'!C:C,'GSC Data 16mo'!B:B,">"&S6,'GSC Data 16mo'!B:B,"<"&T6)