Using Subtotal and Countifs together in formula.

JL Allen

New Member
Joined
Jan 16, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Ultimately I want the count of the number of visible rows where the multiple criteria is specified (I don’t want to use Filter for the criteria).

I have numerous sales people with 6 types of sales. I only want to know the count of 3 of the types of sales; i.e., "Red", "Blue", and "Green". I will be using a filter for the names of the sales people. But need a formula for the count of the 3 types of sales. For instance, I want to know that Mary had 3 of the "Red", "Blue", and "Green" types of sales. See uploaded image.

I've tried for hours and can get the total "Red" "Blue" and "Green" sales but my total stays the same when I filter for Mary's name only.
 

Attachments

  • 3 for Mary.PNG
    3 for Mary.PNG
    16.6 KB · Views: 8

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Please check.
Book1
ABCDE
1EmployeeType
2MaryRed
3TomRed
4DeniseBlue
5HollyBlack
6MaryGreen
7DeniseRed
8HollyBlue
9MaryRed
10TomGrey
11DeniseWhite
12HollyBlue
13MaryGrey
14TomRed
15HollyWhite
16Total14
17Mary4
Sheet5
Cell Formulas
RangeFormula
E16E16=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW($B$2:$B$15)-ROW(B2),,1)))
E17E17=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW($B$2:$B$15)-ROW(B2),,1)),--($A$2:$A$15=$D17))

Thanks
 
Upvote 0
Hi & welcome to MrExcel.
How about
Fluff.xlsm
ABCD
1EmployeeType
2MaryRed
3TomRed
4DeniseBlue
5HollyBlack
6MaryGreen
7DeniseRed
8HollyBlue
9MaryRed
10TomGrey
11DeniseWhite
12HollyBlue
13MaryGrey
14TomRed
15HollyWhite
16Total9
Data
Cell Formulas
RangeFormula
D16D16=ROWS(FILTER(B2:B100,(ISNUMBER(XMATCH(B2:B100,{"red","blue","green"})))*(BYROW(B2:B100,LAMBDA(br,SUBTOTAL(103,br)))=1)))


And when filtered
Fluff.xlsm
ABCD
1EmployeeType
2MaryRed
6MaryGreen
9MaryRed
13MaryGrey
16Total3
Data
Cell Formulas
RangeFormula
D16D16=ROWS(FILTER(B2:B100,(ISNUMBER(XMATCH(B2:B100,{"red","blue","green"})))*(BYROW(B2:B100,LAMBDA(br,SUBTOTAL(103,br)))=1)))
 
Upvote 0
Spot on, Fluff! That is exactly what I was looking for. Thank you!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

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