Counting the number of values which meet multiple criteria

[LaurenH256]

Hi all,
I have a project where I need to organise and count values very specifically.
Cases of work are organised into batches - some batches contain several different cases relating to different customers. Some cases are reviewed for quality control purposes and I need to create a count of the number of different cases that have been reviewed in a month. This is a much more simplified version of the data that I am using and need to count:

I already had a pre-existing array to count to number of times the customer ID appeared in each month to form a count:
=SUM(IF(FREQUENCY(IF(Sheet1!$A:$A=F2,Sheet1!$B:$B),Sheet1!$B:$B),1)) - this example gives the value of 2 for October because there are two different Customer IDs.

But it's not become necessary for me to also count the number of different cases that have been checked, even if it is for the same customer. (I think the formula will still need to look at the Customer ID though because the Case Numbers are very often between 1-3, so in my full dataset, just looking at the Case number against the Month would likely not be accurate.

Any advice on this would be much appreciated! Thank you,
Lauren

 

[LaurenH256]

Hi all,
I have a project where I need to organise and count values very specifically.
Cases of work are organised into batches - some batches contain several different cases relating to different customers. Some cases are reviewed for quality control purposes and I need to create a count of the number of different cases that have been reviewed in a month. This is a much more simplified version of the data that I am using and need to count:

View attachment 29173

I already had a pre-existing array to count to number of times the customer ID appeared in each month to form a count:
=SUM(IF(FREQUENCY(IF(Sheet1!$A:$A=F2,Sheet1!$B:$B),Sheet1!$B:$B),1)) - this example gives the value of 2 for October because there are two different Customer IDs.

But it's not become necessary for me to also count the number of different cases that have been checked, even if it is for the same customer. (I think the formula will still need to look at the Customer ID though because the Case Numbers are very often between 1-3, so in my full dataset, just looking at the Case number against the Month would likely not be accurate.

Any advice on this would be much appreciated! Thank you,
Lauren

Apologies. That example image is inaccurate. Updated:

And the working formula I currently have just counting the number of different customer IDs against each month would be :
=SUM(IF(FREQUENCY(IF(Sheet1!$A:$A=E2,Sheet1!$B:$B),Sheet1!$B:$B),1))

Thanks

 

[jasonb75]

Maybe something like this?

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Month	ID	Batch	Case		Oct	Nov	Dec
2	Oct	123	1	1		3	2	1
3	Oct	456	1	1
4	Oct	456	2	1
5	Oct	456	2	2
6	Nov	789	1	1
7	Nov	789	1	2
8	Nov	789	1	2
9	Dec	123	1	1
10	Dec	123	1	1
Sheet3

Cell Formulas
Range	Formula
F2:H2	F2	=ROWS(UNIQUE(FILTER($B$2:$B$10&"|"&$D$2:$D$10,($A$2:$A$10=F$1),"")))

 

[LaurenH256]

Maybe something like this?

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Month	ID	Batch	Case		Oct	Nov	Dec
2	Oct	123	1	1		3	2	1
3	Oct	456	1	1
4	Oct	456	2	1
5	Oct	456	2	2
6	Nov	789	1	1
7	Nov	789	1	2
8	Nov	789	1	2
9	Dec	123	1	1
10	Dec	123	1	1
Sheet3

Cell Formulas
Range	Formula
F2:H2	F2	=ROWS(UNIQUE(FILTER($B$2:$B$10&"|"&$D$2:$D$10,($A$2:$A$10=F$1),"")))

Hi,

Thanks very much for your suggestion. It seems to work for counting the months that have data in, but doesn't look quite right for months that have yet to be completed as it's counting as 1 when no cases have yet been reviewed, e.g.

 

[jasonb75]

That can be easily rectified by removing the blank from the filter to force an error when there are no results.

=IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$10&"|"&$D$2:$D$10,($A$2:$A$10=F$1)))),0)

 

[LaurenH256]

That seems to work perfectly - thank you so much!