# 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")&")"

#### navic

##### Active Member
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).
Code:
``=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
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
="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).
Code:
``=IFERROR(INDEX(Sheet4!\$Q\$3:\$Q\$20,MATCH(0,COUNTIF(\$A\$2:A2,Sheet4!\$Q\$3:\$Q\$20&""),0)),0)``

#### Attachments

• Newbienew4.png
12.3 KB · Views: 3

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Newbienew

##### Active Member
I shall give this an try @ Navic

Replies
0
Views
161
Replies
6
Views
91
Replies
1
Views
138
Replies
6
Views
145
Replies
2
Views
76

1,129,569
Messages
5,637,098
Members
416,957
Latest member
Brovashift

### 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