Is there a way to emulate COUNTIF in a filtered table?

[adambc]

I’ve seen a number of SUMPRODUCT based workarounds, but they don’t seem to allow me to use TableName[ColumnName]?

I have a table that is the result of taking data from 3 source tables, which is then heavily filtered (multi column filtering, c. 2000 down to c. 750 records) to only display the records required (and before you ask, PowerQuery doesn’t do it because sometimes I need to unstack the filtering so cannot loose the filtered records) …

I have 2 columns (EmailAddress and TrainingCompleted) that I want to use to derive a % …

EmailAddress is easy because it’s email addresses so I can use =SUBTOTAL(103, Table1[EmailAddress]) …

But TrainingCompleted is a problem because it contains ONLY ”TRUE” or “FALSE” which are the result of an ISNUMBER based formula so =COUNTIF(Table1[TrainingCompleted], “TRUE”) picks up filtered AND non filtered rows …

Is there a way I can get a total of the number of rows with “TRUE” in TrainingCompleted, but only the filtered rows?

Thanks …

 

[Dave Patton]

Does Subtotal 103 not work on that column?

 

[adambc]

Does Subtotal 103 not work on that column?

No, because it picks up every value ie “TRUE” and “FALSE” - unless you know of a way to specify only when value = “TRUE”?

 

[Fluff]

Why not create a helper column using

=subtotal(103,[@TrainingCompleted])

then you can add that column as an extra criteria in your Countifs

 

[Dave Patton]

The Subtotal will work or you could use SumProduct.

T202303a.xlsm
	A	B	C
1		4
2	TRUE	3
3	Percentage	75%
4	Email	Training	Year
11	yyy	TRUE	2022
12	yyy	TRUE	2023
13	yyy	TRUE	2022
14	yyy	FALSE	2022
15
1e

Cell Formulas
Range	Formula
B1	B1	=SUBTOTAL(103,Table2[Training])
B2	B2	=SUMPRODUCT(--(Table2[Email]="yyy"),--(Table2[Training]=TRUE))
B3	B3	=B2/B1
B14	B14	=C14=YEAR(TODAY())

 

[adambc]

The Subtotal will work or you could use SumProduct.

T202303a.xlsm
	A	B	C
1		4
2	TRUE	3
3	Percentage	75%
4	Email	Training	Year
11	yyy	TRUE	2022
12	yyy	TRUE	2023
13	yyy	TRUE	2022
14	yyy	FALSE	2022
15
1e

Cell Formulas
Range	Formula
B1	B1	=SUBTOTAL(103,Table2[Training])
B2	B2	=SUMPRODUCT(--(Table2[Email]="yyy"),--(Table2[Training]=TRUE))
B3	B3	=B2/B1
B14	B14	=C14=YEAR(TODAY())

@Dave Patton

My filtered list has unique email addresses in the Email column, so not sure this will work?

But thanks for trying to help me …

 

[adambc]

Why not create a helper column using

=subtotal(103,[@TrainingCompleted])

then you can add that column as an extra criteria in your Countifs

@Fluff

Was hoping there was a solution without using a helper column - but always thought this would be the only answer!

Thanks for the reply …

 

[Dave Patton]

N.B. You did not provide any sample data or examples of your calculations.
You can post a concise version of your sheet with the forum's tool named XL2BB.

Instead of showing True or False, coerce the formulas that Yield True or False to show 1 or 0.
This is often done with double negative or +0
=--(formula xxx)

T202303a.xlsm
	A	B
18	Completed	50%
19
20	Email	Training
21	xxx	1
25	xxx4	1
27	yyy	0
28	yyy	0
29	yyy	1
30	yyy	0
1e

Cell Formulas
Range	Formula
B18	B18	=SUBTOTAL(109,Table22[Training])/SUBTOTAL(103,Table22[Training])

T202303a.xlsm
	A	B
17		6
18	TRUE is 1	3
19	Percentage	50%
20	Email	Training
21	xxx	1
25	xxx4	1
27	yyy	0
28	yyy	0
29	yyy	1
30	yyy	0
31
1e

Cell Formulas
Range	Formula
B17	B17	=SUBTOTAL(103,Table22[Training])
B18	B18	=SUBTOTAL(109,Table22[Training])
B19	B19	=B18/B17

 

[adambc]

N.B. You did not provide any sample data or examples of your calculations.
You can post a concise version of your sheet with the forum's tool named XL2BB.

Instead of showing True or False, coerce the formulas that Yield True or False to show 1 or 0.
This is often done with double negative or +0
=--(formula xxx)

T202303a.xlsm
	A	B
18	Completed	50%
19
20	Email	Training
21	xxx	1
25	xxx4	1
27	yyy	0
28	yyy	0
29	yyy	1
30	yyy	0
1e

Cell Formulas
Range	Formula
B18	B18	=SUBTOTAL(109,Table22[Training])/SUBTOTAL(103,Table22[Training])

T202303a.xlsm
	A	B
17		6
18	TRUE is 1	3
19	Percentage	50%
20	Email	Training
21	xxx	1
25	xxx4	1
27	yyy	0
28	yyy	0
29	yyy	1
30	yyy	0
31
1e

Cell Formulas
Range	Formula
B17	B17	=SUBTOTAL(103,Table22[Training])
B18	B18	=SUBTOTAL(109,Table22[Training])
B19	B19	=B18/B17

@Dave Patton

That might just work, will let you know …

Many thanks …

 

[Dave Patton]

I intended to post the following

T202303a.xlsm
	A	B
1
2	Completed	50%
3
4	Email	Training
5	xxx	1
9	xxx4	1
11	yyy	0
12	yyy	0
13	yyy	1
14	yyy	0
15
1e

Cell Formulas
Range	Formula
B2	B2	=SUBTOTAL(109,Table1[Training])/SUBTOTAL(103,Table1[Email])