Using Subtotal and Countifs together in formula.

[JL Allen]

Ultimately I want the count of the number of visible rows where the multiple criteria is specified (I don’t want to use Filter for the criteria).

I have numerous sales people with 6 types of sales. I only want to know the count of 3 of the types of sales; i.e., "Red", "Blue", and "Green". I will be using a filter for the names of the sales people. But need a formula for the count of the 3 types of sales. For instance, I want to know that Mary had 3 of the "Red", "Blue", and "Green" types of sales. See uploaded image.

I've tried for hours and can get the total "Red" "Blue" and "Green" sales but my total stays the same when I filter for Mary's name only.

 

[Sam_D_Ben]

Please check.

Book1
	A	B	C	D	E
1	Employee	Type
2	Mary	Red
3	Tom	Red
4	Denise	Blue
5	Holly	Black
6	Mary	Green
7	Denise	Red
8	Holly	Blue
9	Mary	Red
10	Tom	Grey
11	Denise	White
12	Holly	Blue
13	Mary	Grey
14	Tom	Red
15	Holly	White
16				Total	14
17				Mary	4
Sheet5

Cell Formulas
Range	Formula
E16	E16	=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW($B$2:$B$15)-ROW(B2),,1)))
E17	E17	=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW($B$2:$B$15)-ROW(B2),,1)),--($A$2:$A$15=$D17))

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D
1	Employee	Type
2	Mary	Red
3	Tom	Red
4	Denise	Blue
5	Holly	Black
6	Mary	Green
7	Denise	Red
8	Holly	Blue
9	Mary	Red
10	Tom	Grey
11	Denise	White
12	Holly	Blue
13	Mary	Grey
14	Tom	Red
15	Holly	White
16			Total	9
Data

Cell Formulas
Range	Formula
D16	D16	=ROWS(FILTER(B2:B100,(ISNUMBER(XMATCH(B2:B100,{"red","blue","green"})))*(BYROW(B2:B100,LAMBDA(br,SUBTOTAL(103,br)))=1)))

And when filtered

Fluff.xlsm
	A	B	C	D
1	Employee	Type
2	Mary	Red
6	Mary	Green
9	Mary	Red
13	Mary	Grey
16			Total	3
Data

Cell Formulas
Range	Formula
D16	D16	=ROWS(FILTER(B2:B100,(ISNUMBER(XMATCH(B2:B100,{"red","blue","green"})))*(BYROW(B2:B100,LAMBDA(br,SUBTOTAL(103,br)))=1)))

 

[JL Allen]

Spot on, Fluff! That is exactly what I was looking for. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.