mutiple countifs statements excel 2016

[jon78981]

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 A

SALES HOME MEMBER ColumnB

SALES AWAY MEMBER Column C

DATA1 Column D

sale Column E

SELECTED SALES HOME MEMBER Column F

DATA1 BAND Column G

NUMBER occurrences Column H

 

[Fluff]

Hi & welcome to MrExcel.
Can you have anything in col G other than those bands you have mentioned?

 

[jon78981]

Date Column A

SALES HOME MEMBER B

SALES AWAY MEMBER Columnc

DATA1 Column D

sale Column E

SELECTED SALES HOME MEMBER Column F

DATA1 BAND Column G

NUMBER occurences Column H

08 March 2020

VICKY

LENNY

180.00

1​

VICKY

176-200

1

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]

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]

Can you please answer my question?

 

[jon78981]

trying to post the table again with correct alignment

Date	SALES HOME MEMBER	SALES AWAY MEMBER	DATA1	sale	SELECTED SALES HOME MEMBER	DATA1 BAND	NUMBER SALES
08 March 2020	VICKY	LENNY	180.00	1​	VICKY	176-200	1

 

[Fluff]

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

 

[jon78981]

anon DATA.xlsm
	A	B	C	D	E	F	G	H
1	Date	SALES HOME MEMBER	SALES AWAY MEMBER	DATA1	sale	SELECTED SALES HOME MEMBER	DATA1 BAND	NUMBER SALES
2	01 March 2020	EMMA	ANTONY	460.00	1	EMMA	GREATER THAN 300
3	01 March 2020	MIKE	GARRY	380.00	0	MIKE	GREATER THAN 300
4	01 March 2020	RONNY	BARRY	230.00	1	RONNY	226-250
5	01 March 2020	SILVIA	OSCAR	150.00	1	SILVIA	150-175
6	06 March 2020	ALVIN	VELVET	290.00	1	ALVIN	276-300
7	07 March 2020	ANTONY	SILVIA	198.00	1	ANTONY	176-200
8	07 March 2020	BARRY	SONNYA	139.00	0	BARRY	LESS Than 150
9	07 March 2020	EUGINE	MIKE	176.00	1	EUGINE	176-200
10	07 March 2020	GARRY	CIRIL	189.00	0	GARRY	176-200
11	08 March 2020	BETTY	RONNY	460.00	1	BETTY	GREATER THAN 300
12	08 March 2020	LARRY	GARETH	210.00	1	LARRY	201-225
13	08 March 2020	OSCAR	EMMA	254.00	0	OSCAR	252-275
14	08 March 2020	VALERIE	AMBER	305.00	1	VALERIE	GREATER THAN 300
15	08 March 2020	VICKY	LENNY	180.00	1	VICKY	176-200	1
16	10 March 2020	EUGINE	SONNYA	340.00	1	EUGINE	GREATER THAN 300
Sheet1

Cell Formulas
Range	Formula
H15	H15	=COUNTIFS(B$2:B16,"="&F15,F2:F16,"VICKY",G$2:G16,"176-200",A$2:A16,"<="&A15,E$2:E16,1)

 

[Fluff]

Thanks for that, now can you please answer my question?

 

[jon78981]

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.