Counting the number of values which meet multiple criteria

LaurenH256

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:

1609934260670.png


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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

LaurenH256

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:

1609935047148.png


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

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
Maybe something like this?
Book1 (version 1).xlsb
ABCDEFGH
1MonthIDBatchCaseOctNovDec
2Oct12311321
3Oct45611
4Oct45621
5Oct45622
6Nov78911
7Nov78912
8Nov78912
9Dec12311
10Dec12311
Sheet3
Cell Formulas
RangeFormula
F2:H2F2=ROWS(UNIQUE(FILTER($B$2:$B$10&"|"&$D$2:$D$10,($A$2:$A$10=F$1),"")))
 

LaurenH256

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Maybe something like this?
Book1 (version 1).xlsb
ABCDEFGH
1MonthIDBatchCaseOctNovDec
2Oct12311321
3Oct45611
4Oct45621
5Oct45622
6Nov78911
7Nov78912
8Nov78912
9Dec12311
10Dec12311
Sheet3
Cell Formulas
RangeFormula
F2:H2F2=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.

1609939593937.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
That can be easily rectified by removing the blank from the filter to force an error when there are no results.
Excel Formula:
=IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$10&"|"&$D$2:$D$10,($A$2:$A$10=F$1)))),0)
 
Solution

LaurenH256

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
That seems to work perfectly - thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,303
Messages
5,595,324
Members
413,986
Latest member
Elizsk

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