# Countif with subtotal

#### Newbienew

##### Active Member
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
ABCDEFGHIJKLMNOPQRSTUVWXY
2A (5)B (5)C (5)Total (15)E (5)
312-Dec-21aWHEN I FILTER I WOULD LIKE EACH ONE OF THESE NUMBERS TO CHANGE WITH SUBTOTAL FORMULA
412-Jan-21b
512-Feb-21c
612-Dec-21d
712-Jan-21e
812-Feb-21A
912-Dec-21C
1012-Jan-21E
1112-Feb-21D
1212-Dec-21B
1312-Jan-21A
1412-Feb-21E
1512-Dec-21D
1612-Jan-21B
1712-Feb-21E
1812-Dec-21C
1912-Jan-21C
2012-Feb-21A
2112-Dec-21D
2212-Jan-21B
2312-Feb-21A
2412-Dec-21B
2512-Jan-21C
2612-Feb-21D
2712-Dec-21E
28
Sheet1
Cell Formulas
RangeFormula
U2U2="A ("&COUNTIF(\$Q\$3:\$Q\$27,"a")&")"
V2V2="B ("&COUNTIF(\$Q\$3:\$Q\$27,"B")&")"
W2W2="C ("&COUNTIF(\$Q\$3:\$Q\$27,"C")&")"
X2X2="Total ("&SUM(COUNTIF(\$Q\$3:\$Q\$27,"a"),COUNTIF(\$Q\$3:\$Q\$27,"b"),COUNTIF(\$Q\$3:\$Q\$27,"c"))&")"
Y2Y2="E ("&COUNTIF(\$Q\$3:\$Q\$27,"E")&")"

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

##### Well-known Member
Try This with CTRL+SHIFT+ENTER
Book1
APQTUVWXYZ
1LettersA (5)B (5)C (5)Total (15)E (5)
212/12/2021aWHEN I FILTER I WOULD LIKE EACH ONE OF THESE NUMBERS TO CHANGE WITH SUBTOTAL FORMULA
31/12/2021b
42/12/2021c
512/12/2021d
61/12/2021e
72/12/2021A
812/12/2021C
91/12/2021E
102/12/2021D
1112/12/2021B
121/12/2021A
132/12/2021E
1412/12/2021D
151/12/2021B
162/12/2021E
1712/12/2021C
181/12/2021C
192/12/2021A
2012/12/2021D
211/12/2021B
222/12/2021A
2312/12/2021B
241/12/2021C
252/12/2021D
2612/12/2021E
27
Sheet2
Cell Formulas
RangeFormula
U1U1="A ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$2:\$Q\$26,ROW(\$Q\$2:\$Q\$26)-MIN(ROW(\$Q\$2:\$Q\$26)),,1))*(\$Q\$2:\$Q\$26="A"))&")"
V1V1="B ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$2:\$Q\$26,ROW(\$Q\$2:\$Q\$26)-MIN(ROW(\$Q\$2:\$Q\$26)),,1))*(\$Q\$2:\$Q\$26="B"))&")"
W1W1="C ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$2:\$Q\$26,ROW(\$Q\$2:\$Q\$26)-MIN(ROW(\$Q\$2:\$Q\$26)),,1))*(\$Q\$2:\$Q\$26="C"))&")"
X1X1="Total (" & SUM(MID(U1,4,LEN(U1)-FIND(")",U1)+1),MID(V1,4,LEN(V1)-FIND(")",V1)+1),MID(W1,4,LEN(W1)-FIND(")",W1)+1))&")"
Y1Y1="E ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$2:\$Q\$26,ROW(\$Q\$2:\$Q\$26)-MIN(ROW(\$Q\$2:\$Q\$26)),,1))*(\$Q\$2:\$Q\$26="E"))&")"
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Newbienew

##### Active Member
WOW, that is mind blowing. Thank you so much

##### Well-known Member
You're Welcome & Thanks for Feedback.

#### Newbienew

##### Active Member

Ran a ground with the totals formula. A,B,C,D have been changed AFFILIATED, MANPOWER, LCPO replace A, B, C. is there somehting different that ned to be done?
I did hit Crtl ****f Enter.

##### Well-known Member
For Total formula you don't need Press CTRL+SHIFT+ENTER
it should be work. Are you sure your Data don't have more spaces at first or end of words?

#### navic

##### Active Member

You can try a different way

Create Unique list (as auxiliary data)
In the picture below 'U11:AB11' range
Array formula in the cell 'U11' (copy to the right)
Code:
``=INDEX(\$B\$2:\$B\$26,MATCH(0,COUNTIF(\$T\$11:T11,\$B\$2:\$B\$26&""),0))``
You can move this help data (T11:AB11) wherever you want.

The formulas are as follows:

Array formula in the cell 'U1' (copy to 'Y1')
Code:
``=INDEX(\$B\$2:\$B\$26,MATCH(0,COUNTIF(\$T\$11:T11,\$B\$2:\$B\$26&""),0))&" ("&COUNTIF(\$B\$2:\$B\$26,INDEX(\$B\$2:\$B\$26,MATCH(0,COUNTIF(\$T\$11:T11,\$B\$2:\$B\$26&""),0)))&")"``

Formula in the cell 'Z1'
Code:
``="Total ("&SUM(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(U1,4)),"(",""),")","")*1,SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(V1,4)),"(",""),")","")*1,SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(W1,4)),"(",""),")","")*1,SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(X1,4)),"(",""),")","")*1,SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(Y1,4)),"(",""),")","")*1)&")"``
or in the cell 'Z3'
Code:
``="Total ("&SUM(COUNTIF(\$B\$2:\$B\$26,U11),COUNTIF(\$B\$2:\$B\$26,V11),COUNTIF(\$B\$2:\$B\$26,W11),COUNTIF(\$B\$2:\$B\$26,X11),COUNTIF(\$B\$2:\$B\$26,Y11))&")"``

#### Attachments

• Newbienew.png
14.1 KB · Views: 4

#### Newbienew

##### Active Member
@maabadi This is what I have.
Countif ssubtotal.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
2Affiliated (4)LCPO (3)MANPOWER (4)#VALUE!TA (6)
412-Jan-21TA
712-Jan-21MANPOWER
1012-Jan-21TA
1312-Jan-21TA
1612-Jan-21MANPOWER
1912-Jan-21AFFILIATED
2212-Jan-21LCPO
2512-Jan-21LCPO
28AFFILIATED< SHOULDN'T BE COUNTED
29AFFILIATED< SHOULDN'T BE COUNTED
30AFFILIATED< SHOULDN'T BE COUNTED
31TA< SHOULDN'T BE COUNTED
32TA< SHOULDN'T BE COUNTED
33TA< SHOULDN'T BE COUNTED
34MANPOWER< SHOULDN'T BE COUNTED
35MANPOWER< SHOULDN'T BE COUNTED
36LCPO< SHOULDN'T BE COUNTED
Sheet1
Cell Formulas
RangeFormula
U2U2="Affiliated ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$2:\$Q\$40,ROW(\$Q\$2:\$Q\$40)-MIN(ROW(\$Q\$2:\$Q\$40)),,1))*(\$Q\$2:\$Q\$40="Affiliated"))&")"
V2V2="LCPO ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$2:\$Q\$40,ROW(\$Q\$2:\$Q\$40)-MIN(ROW(\$Q\$2:\$Q\$40)),,1))*(\$Q\$2:\$Q\$40="LCPO"))&")"
W2W2="MANPOWER ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$2:\$Q\$40,ROW(\$Q\$2:\$Q\$40)-MIN(ROW(\$Q\$2:\$Q\$40)),,1))*(\$Q\$2:\$Q\$40="MANPOWER"))&")"
X2X2="Total (" & SUM(MID(U2,4,LEN(U2)-FIND(")",U2)+1),MID(V2,4,LEN(V2)-FIND(")",V2)+1),MID(W2,4,LEN(W2)-FIND(")",W2)+1))&")"
Y2Y2="TA ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$2:\$Q\$40,ROW(\$Q\$2:\$Q\$40)-MIN(ROW(\$Q\$2:\$Q\$40)),,1))*(\$Q\$2:\$Q\$40="TA"))&")"
Press CTRL+SHIFT+ENTER to enter array formulas.

@navic Are you suggesting having a key on another sheet to read it from? Just want to make sure. Im trying to envision your method

##### Well-known Member
if you want don't Count until you import Date at Column A then Add this Parts to SumProduct Formula: *(\$A\$3:\$A\$41<>"")
Total Formula Corrected.
Book1.xlsx
AQUVWXYZ
2Affiliated (1)LCPO (2)MANPOWER (2)Total (5)TA (3)
41/12/2021TA
71/12/2021MANPOWER
101/12/2021TA
131/12/2021TA
161/12/2021MANPOWER
191/12/2021AFFILIATED
221/12/2021LCPO
251/12/2021LCPO
28AFFILIATED
29AFFILIATED
30AFFILIATED
31TA
32TA
33TA
34MANPOWER
35MANPOWER
36LCPO
37
38
Sheet1
Cell Formulas
RangeFormula
U2U2="Affiliated ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$3:\$Q\$41,ROW(\$Q\$3:\$Q\$41)-MIN(ROW(\$Q\$3:\$Q\$41)),,1))*(\$Q\$3:\$Q\$41="Affiliated")*(\$A\$3:\$A\$41<>""))&")"
V2V2="LCPO ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$3:\$Q\$41,ROW(\$Q\$3:\$Q\$41)-MIN(ROW(\$Q\$3:\$Q\$41)),,1))*(\$Q\$3:\$Q\$41="LCPO")*(\$A\$3:\$A\$41<>""))&")"
W2W2="MANPOWER ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$3:\$Q\$41,ROW(\$Q\$3:\$Q\$41)-MIN(ROW(\$Q\$3:\$Q\$41)),,1))*(\$Q\$3:\$Q\$41="MANPOWER")*(\$A\$3:\$A\$41<>""))&")"
X2X2="Total (" & SUM(MID(U2,FIND("(",U2) + 1,LEN(U2)-FIND(")",U2)+1),MID(V2,FIND("(",V2) + 1,LEN(V2)-FIND(")",V2)+1),MID(W2,FIND("(",W2) + 1,LEN(W2)-FIND(")",W2)+1))&")"
Y2Y2="TA ("&SUMPRODUCT(SUBTOTAL(3,OFFSET(\$Q\$3:\$Q\$41,ROW(\$Q\$3:\$Q\$41)-MIN(ROW(\$Q\$3:\$Q\$41)),,1))*(\$Q\$3:\$Q\$41="TA")*(\$A\$3:\$A\$41<>""))&")"
Press CTRL+SHIFT+ENTER to enter array formulas.

#### navic

##### Active Member
Do everything on the same Worksheet.
Afterwards, you can move the unique helper data to another Sheet.

Do it as follows.

- select the range 'T10: AB11' in my previous post
- Cut / Paste on another Sheet
(Attention! Cells 'T10 and T11' are empty)

Excel will automatically adjust the formulas.

In this example in the image I moved the auxiliary Unique data from Sheet2 to Sheet3.

The formulas are as follows:

Cell 'U1' contains the ARRAY formula (Finished with Ctrl + Shift + Enter). Copy formula to 'Y1'
Code:
``=IFERROR(INDEX(\$B\$2:\$B\$26,MATCH(0,COUNTIF(Sheet3!\$A\$2:A2,\$B\$2:\$B\$26&""),0))&" ("&COUNTIF(\$B\$2:\$B\$26,INDEX(\$B\$2:\$B\$26,MATCH(0,COUNTIF(Sheet3!\$A\$2:A2,\$B\$2:\$B\$26&""),0)))&")","")``
For "Total", you can use one of the formulas listed below.
The formula in the 'Z1' cell. This formula contains multiple 'Substitute' Excel functions nested in SUM function.
Code:
``="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 the formula in the 'Z3' cell. This formula contains multiple 'Countif' Excel functions nested in SUM function.
Code:
``="Total ("&SUM(COUNTIF(\$B\$2:\$B\$26,Sheet3!B2),COUNTIF(\$B\$2:\$B\$26,Sheet3!C2),COUNTIF(\$B\$2:\$B\$26,Sheet3!D2),COUNTIF(\$B\$2:\$B\$26,Sheet3!E2),COUNTIF(\$B\$2:\$B\$26,Sheet3!F2))&")"``

On Sheet3, the data looks like the image below and the ARRAY formula in the 'B2' cell is. (Copy the formula to the right).
Code:
``=IFERROR(INDEX(Sheet2!\$B\$2:\$B\$26,MATCH(0,COUNTIF(\$A\$2:A2,Sheet2!\$B\$2:\$B\$26&""),0)),0)``

I hope you will manage if you choose this option to solve your task.

#### Attachments

• Newbienew2.png
12.5 KB · Views: 2
• Newbienew3.png
8.1 KB · Views: 2

Replies
1
Views
50
Replies
1
Views
32
Replies
5
Views
113
Replies
0
Views
162
Replies
6
Views
102

1,130,208
Messages
5,640,856
Members
417,174
Latest member
diegomuser

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back