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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Can you have anything in col G other than those bands you have mentioned?
 

jon78981

New Member
Joined
Jun 26, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Date Column ASALES HOME MEMBER BSALES AWAY MEMBER ColumncDATA1 Column Dsale Column ESELECTED SALES HOME MEMBER Column FDATA1 BAND Column GNUMBER occurences Column H
08 March 2020VICKYLENNY180.00
1​
VICKY176-2001


I have poupulated how 1 row of data would look like
. I think i need the Column G to remain how it is as that is identifying what band Column D falls into. There are the 8 various bands that data from ColumnD could fall into.
Ive realised made mistake with Column description heading in my original post. Corrected below.


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 D 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 D 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 D 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 D 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 D 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 D 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 D 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 D band oF GREATER THAN 300, The date is equal to or less than the current date.
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
RE:
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.

Have you gone through the Evaluate Formula tool to see where the VALUE#! Error gets tripped?
 

Fluff

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

ADVERTISEMENT

Can you please answer my question?
 

jon78981

New Member
Joined
Jun 26, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
trying to post the table again with correct alignment

DateSALES HOME MEMBERSALES AWAY MEMBERDATA1saleSELECTED SALES HOME MEMBERDATA1 BANDNUMBER SALES
08 March 2020VICKYLENNY180.00
1​
VICKY176-2001
 

Fluff

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

ADVERTISEMENT

To post data to the site please use the XL2BB add-in.
 

jon78981

New Member
Joined
Jun 26, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
anon DATA.xlsm
ABCDEFGH
1DateSALES HOME MEMBERSALES AWAY MEMBERDATA1saleSELECTED SALES HOME MEMBERDATA1 BANDNUMBER SALES
201 March 2020EMMAANTONY460.001EMMAGREATER THAN 300
301 March 2020MIKEGARRY380.000MIKEGREATER THAN 300
401 March 2020RONNYBARRY230.001RONNY226-250
501 March 2020SILVIAOSCAR150.001SILVIA150-175
606 March 2020ALVINVELVET290.001ALVIN276-300
707 March 2020ANTONYSILVIA198.001ANTONY176-200
807 March 2020BARRYSONNYA139.000BARRYLESS Than 150
907 March 2020EUGINEMIKE176.001EUGINE176-200
1007 March 2020GARRYCIRIL189.000GARRY176-200
1108 March 2020BETTYRONNY460.001BETTYGREATER THAN 300
1208 March 2020LARRYGARETH210.001LARRY201-225
1308 March 2020OSCAREMMA254.000OSCAR252-275
1408 March 2020VALERIEAMBER305.001VALERIEGREATER THAN 300
1508 March 2020VICKYLENNY180.001VICKY176-2001
1610 March 2020EUGINESONNYA340.001EUGINEGREATER THAN 300
Sheet1
Cell Formulas
RangeFormula
H15H15=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"176-200",A$2:A16,"<="&A15,E$2:E16,1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, now can you please answer my question?
 

jon78981

New Member
Joined
Jun 26, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
Can you have anything in col G other than those bands you have mentioned?
Column G i think for formula to work has to remain like that. Column G is caterogorizing the numbers in Column D into the 8 possible bands. The 8 different bands is the main point of the excelsheet investigation. But if it can be done by other means with something else in column G that all for that.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,285
Messages
5,635,314
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