Countif with subtotal

[Newbienew]

Hello all,

I am looking to count specific text when filtered. I am having the hardest time trying to piece the formula together. I have attached a link to of my example.

Countif ssubtotal.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
2																					A (5)	B (5)	C (5)	Total (15)	E (5)
3	12-Dec-21																a				WHEN I FILTER I WOULD LIKE EACH ONE OF THESE NUMBERS TO CHANGE WITH SUBTOTAL FORMULA
4	12-Jan-21																b
5	12-Feb-21																c
6	12-Dec-21																d
7	12-Jan-21																e
8	12-Feb-21																A
9	12-Dec-21																C
10	12-Jan-21																E
11	12-Feb-21																D
12	12-Dec-21																B
13	12-Jan-21																A
14	12-Feb-21																E
15	12-Dec-21																D
16	12-Jan-21																B
17	12-Feb-21																E
18	12-Dec-21																C
19	12-Jan-21																C
20	12-Feb-21																A
21	12-Dec-21																D
22	12-Jan-21																B
23	12-Feb-21																A
24	12-Dec-21																B
25	12-Jan-21																C
26	12-Feb-21																D
27	12-Dec-21																E
28
Sheet1

Cell Formulas
Range	Formula
U2	U2	="A ("&COUNTIF($Q$3:$Q$27,"a")&")"
V2	V2	="B ("&COUNTIF($Q$3:$Q$27,"B")&")"
W2	W2	="C ("&COUNTIF($Q$3:$Q$27,"C")&")"
X2	X2	="Total ("&SUM(COUNTIF($Q$3:$Q$27,"a"),COUNTIF($Q$3:$Q$27,"b"),COUNTIF($Q$3:$Q$27,"c"))&")"
Y2	Y2	="E ("&COUNTIF($Q$3:$Q$27,"E")&")"

 

[navic]

Hi @Newbienew

I'm sorry, I didn't immediately notice that you set a condition in the 'A' column (blank or empty)
So, "shouldn't be counted" if there is no data in the same row in the 'A' column.

The new situation is a combination of my formulas and the formula given to you by @maabadi
In my proposal, you don't have to define a 'string' in every formula, but you pull that information from the helper sheet.

In this post, the basic data is on Sheet4 and the auxiliary data (unique) is on Sheet3

The formulas are as follows:

- Sheet4
The ARRAY formula in the 'U1' cell is below (copy the formula to the right).

=Sheet3!B2&" ("&SUMPRODUCT(SUBTOTAL(3,OFFSET($Q$3:$Q$41,ROW($Q$3:$Q$41)-MIN(ROW($Q$3:$Q$41)),,1))*($Q$3:$Q$41=Sheet3!B2)*($A$3:$A$41<>""))&")"

For a total formula you can use one of the two below or a third that SUM the values

="Total ("&SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(U1,4)),"(",""),")","")*1,0),IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(V1,4)),"(",""),")","")*1,0),IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(W1,4)),"(",""),")","")*1,0),IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(X1,4)),"(",""),")","")*1,0),IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(Y1,4)),"(",""),")","")*1,0))&")"
or
="Total (" & SUM(MID(U1,FIND("(",U1) + 1,LEN(U1)-FIND(")",U1)+1),MID(V1,FIND("(",V1) + 1,LEN(V1)-FIND(")",V1)+1),MID(W1,FIND("(",W1) + 1,LEN(W1)-FIND(")",W1)+1),MID(X1,FIND("(",X1) + 1,LEN(X1)-FIND(")",X1)+1))&")"

- Sheet3
ARRAY formula in 'B2' cell (copy formula to right). You can see the image in the previous post (Newbienew3.png).

=IFERROR(INDEX(Sheet4!$Q$3:$Q$20,MATCH(0,COUNTIF($A$2:A2,Sheet4!$Q$3:$Q$20&""),0)),0)

 

[Newbienew]

I shall give this an try @ Navic