Subtotal

[itsgrady]

Seems like every time I work within a table the calculations are a little different if not in a table.

Trying to get a subtotal of one column in a table that counts all days greater than 15.

I tried both ways and I may be wrong both ways.

Subtotal(103,Blanks__CVS[Days]>15)

Subtotal(103,Blanks__CVS[Days,Blanks__CVS[Days]>15)

Thanks for you help.

 

[Jeffrey Mahoney]

Let me share a trick I learned. I created a new column that uses Subtotal on each line to determine of the line is visible. Now I can use the column in my criteria to determine if it needs to be counted. You can use your table filter and the formula to count over 15 will only count those rows that are visible.

qLander 20230905.xlsm
	A	B	C	D
1			Count over 15
2			5
3	One	Two	Days	Visible
4	1	13	1	1
5	2	16	15	1
6	3	19	14	1
7	4	22	13	1
8	5	25	20	1
9	6	28	21	1
10	7	31	4	1
11	8	34	8	1
12	9	37	7	1
13	10	40	30	1
14	11	43	31	1
15	12	46	32	1
16	13	49	14	1
17	14	52	10	1
Sheet2

Cell Formulas
Range	Formula
C2	C2	=SUM(COUNTIF(Blanks_CVS[Days],(Blanks_CVS[Days]>15)*(Blanks_CVS[Visible]=1)))
B5:B17	B5	=B4-5+2*4
D4:D17	D4	=SUBTOTAL(102,C4)

 

[itsgrady]

Seems like every time I work within a table the calculations are a little different if not in a table.

Trying to get a subtotal of one column in a table that counts all days greater than 15.

I tried both ways and I may be wrong both ways.

Subtotal(103,Blanks__CVS[Days]>15)

Subtotal(103,Blanks__CVS[Days,Blanks__CVS[Days]>15)

Thanks for you help.

Let me share a trick I learned. I created a new column that uses Subtotal on each line to determine of the line is visible. Now I can use the column in my criteria to determine if it needs to be counted. You can use your table filter and the formula to count over 15 will only count those rows that are visible.

qLander 20230905.xlsm
	A	B	C	D
1			Count over 15
2			5
3	One	Two	Days	Visible
4	1	13	1	1
5	2	16	15	1
6	3	19	14	1
7	4	22	13	1
8	5	25	20	1
9	6	28	21	1
10	7	31	4	1
11	8	34	8	1
12	9	37	7	1
13	10	40	30	1
14	11	43	31	1
15	12	46	32	1
16	13	49	14	1
17	14	52	10	1
Sheet2

Cell Formulas
Range	Formula
C2	C2	=SUM(COUNTIF(Blanks_CVS[Days],(Blanks_CVS[Days]>15)*(Blanks_CVS[Visible]=1)))
B5:B17	B5	=B4-5+2*4
D4:D17	D4	=SUBTOTAL(102,C4)

Thanks for the help. I will need to give this a try. It seems that ECCEL would have an easy way to do this.

 

[Fluff]

Another option also using a helper column

=COUNTIFS(Blanks_CVS[Days],">15",Blanks_CVS[Visible],1)

 

[itsgrady]

Another option also using a helper column

=COUNTIFS(Blanks_CVS[Days],">15",Blanks_CVS[Visible],1)

Thanks for the help with the subtotal problem. Your solutions are always easy to follow and very helpful. Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.