Countif with subtotal

N

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
376
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")&")"
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
    Newbienew.png
    14.1 KB · Views: 5
Upvote 0
@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
 
Upvote 0
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.
 
Upvote 0
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
    Newbienew2.png
    12.5 KB · Views: 3
  • Newbienew3.png
    Newbienew3.png
    8.1 KB · Views: 3
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Conditional summing more complex than my brain
Replies
2
Views
130
Fluff
Fluff
J
  • Solved
help finding my error
Replies
1
Views
151
Proulxs
P
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
235
mst3kr
M
J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
983
felixstraube
F
S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
432
samide2001
S

Forum statistics

Threads
1,214,921
Messages
6,122,280
Members
449,075
Latest member
staticfluids

Share this page

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
Back
Top