mutiple countifs statements excel 2016

jon78981

New Member
Joined
Jun 26, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
I have a dataset of 3000 lines. I am trying to count how many times various sets of criteria occurs in that data.
When fully constructed there would be hundreds of COUNTIFS conditions. I have written a couple COUNTIFS formula but i dont know how to join them together and put all the COUNTIFS statements into one cell so that the counting works correctly.

Or even if the approach of writing hundreds of COUNTIFS into one cell is the correct way to approach this. Once the 100 COUNTIFS statements were in the cell i would drag the formula down/up.
There are 8 conditions to check for in each set


=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"LESS THAN 150",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"150-175",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"176-200",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"201-225",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"226-250",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"252-275",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"276-300",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"GREATER THAN 300",A$2:A16,"<="&A15,E$2:E16,1)

=COUNTIFS(B$2:B16,"="&F15,F2:F16,"EMMA",G$2:G16,"LESS THAN 150",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"EMMA",G$2:G16,"150-175",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"EMMA",G$2:G16,"176-200",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"EMMA",G$2:G16,"201-225",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"EMMA",G$2:G16,"226-250",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"EMMA",G$2:G16,"252-275",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"EMMA",G$2:G16,"276-300",A$2:A16,"<="&A15,E$2:E16,1)
=COUNTIFS(B$2:B16,"="&F15,F2:F16,"EMMA",G$2:G16,"GREATER THAN 300",A$2:A16,"<="&A15,E$2:E16,1)

One formula byitself works, but when i try to join more than one COUNTIFS together i cant get it to accept the way its written.

i have tried =COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"LESS THAN 150",A$2:A16,"<="&A15,E$2:E16,1),COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"150-175",A$2:A16,"<="&A15,E$2:E16,1)
But formula not accepted. Says Value in the box. the comma , after each COUNTIFS does not seem to work.



The top example is for Vicky, I would do same for Emma, and then Ronny etc until id written all the formulas for the various people.
I am trying to put the COUNTIFS formula into cell Column H.

I am trying to count number of sales in Column E. Those cells are populated with 0 or 1
I am trying to count how many sales Column E Vicky has had when she is in the data1 column C band of LESS THAN 150, The date is equal to or less than the current date.
I am trying to count how many sales Column E Vicky has had when she is in the data1 column C band of 150-175, The date is equal to or less than the current date.
I am trying to count how many sales Column E Vicky has had when she is in the data1 column C band of 176-200, The date is equal to or less than the current date.
I am trying to count how many sales Column E Vicky has had when she is in the data1 column C band of 201-225, The date is equal to or less than the current date.
I am trying to count how many sales Column E Vicky has had when she is in the data1 column C band of 226-250, The date is equal to or less than the current date.
I am trying to count how many sales Column E Vicky has had when she is in the data1 column C band of 252-275, The date is equal to or less than the current date.
I am trying to count how many sales Column E Vicky has had when she is in the data1 column C band of 276-300, The date is equal to or less than the current date.
I am trying to count how many sales Column E Vicky has had when she is in the data1 column C band oF GREATER THAN 300, The date is equal to or less than the current date.

Date Column ASALES HOME MEMBER ColumnBSALES AWAY MEMBER Column CDATA1 Column Dsale Column ESELECTED SALES HOME MEMBER Column FDATA1 BAND Column GNUMBER occurrences Column H
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Are you trying to gate a total count of all sales by Vicky if column G contains any of the criteria you have listed?
If so then please answer my question, all it needs is a yes or no.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jon78981

New Member
Joined
Jun 26, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
No. I am trying to gate a total of all sales by Vicky if Column G contains only the one specific band that she fits into. Only count the sales in that one particular number band that she fits into. I dont want to count sales from other numbers bands, only the number band that that cell falls into
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
In that case what's wrong with the formulae you originally posted?
 

jon78981

New Member
Joined
Jun 26, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Having only the one formula only deals with that one particular situation. If she falls into another numbers band, iwant the formula typed into the cell ready to pick up and count the sales for that particular band, I dont want to have to manually determine myself what number band she fits into and then type that formula in. I want the 8 possible forumulas of the bands she could fall into in the cell. So that the formula picks the correct one of those 8 formulas to do the count for that particular number band. And then also the correct set of forumulas in the cell, to deal with another name. So instead of Vicky being in that cell it could be Emma with her set of 8 numbers bands. Hence i was thinking i would need to write approx 100 COUNTIFS, but need the ability to enter them all into the same cell. and Excel to accpet as sitting there ready to comeinto use when the particular situation arises for that particular formula
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I am now completely lost.
At a pure guess see if this does what you want
=COUNTIFS(B$2:B$16,B2,F$2:F$16,B2,G$2:G$16,G2,A$2:A$16,"<="&A2,E$2:E$16,1)

If not please post some sample data, along with expected results.
 

jon78981

New Member
Joined
Jun 26, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
you`ve got it works perfect. Thank you so much
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
😲
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,129,285
Messages
5,635,321
Members
416,852
Latest member
kanaikls

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