Countif with subtotal

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
252
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 14, 2015
Messages
300
Office Version
  1. 2013
Platform
  1. Windows
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
    Newbienew4.png
    12.3 KB · Views: 3

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top