How to sum distinct values in excel using formula

Question

I can't seem to make the other formula suggestions work. I need to stats on how many work items were received for each month. On the summary sheet A, I have a cell indicating the period e.g. 20250101 - 20250131 and next to it a cell where the formula must be added for the number of work items. The data is on sheet B. It contains in column A the date the work item was received and several other columns which must be filtered. Column B contains the work item reference number - I need only the distinct values of this column. Column C contains the Enquiry Type - I need to exclude 'Comms Review' and 'System Controls'. I need the formula to check on sheet A what period it must work with and then check on sheet B for the sum of distinct values in column B, after excluding the two Enquiry Types indicated. The columns can also have blank cells. Is it possible to do it with one formula or will I have to use more than one step?

Sheet A:

| Period | Number of work items received | | 20250101 - 20250131 | sum to calculate distinct values | | --- | --- |

Sheet B: Columns:

Column A<br><br>Date received	Column B<br><br>Reference number	Column C<br><br>Enquiry type	Notes
2025/01/01	22101	Refunds	1st distinct reference number = include in calculation
2025/01/15	22101	Refunds	Repeat reference number = don't include in calculation
2025/01/25	22125	Claims	1st distinct reference number = include in calculation
2025/01/25	22135	Comms Review	Enquiry type exclusion = don't include in calculation
2025/02/02	22136	Refunds	Pall all validations except it is not for the period as indicated on sheet A = don't include in calculation

The data on Sheet A must therefore be as follow:

Period	Number of work items received
20250101 - 20250131	2

Answer 1

Hi, Madeleen Bianco 

Welcome to the Microsoft community.  

I am sincerely grateful to you for the patience with which we are considering this issue. 

Regarding the information you provided, thank you for providing the details about your issue with calculating the number of distinct work items received per month based on data in Sheet B, filtered by period and Enquiry Type, with the results displayed on Sheet A 

Below, I’ll explain the solution and provide a formula tailored to your needs using Microsoft 365’s capabilities. 

It’s possible to do this with a single formula in Microsoft 365 using dynamic array functions. The formula will: 

• Match the period on Sheet A (e.g., 20250101 - 20250131) to dates in Sheet B Column A.
• Filter out 'Comms Review' and 'System Controls' from Sheet B Column C.
• Count the distinct values in Sheet B Column B (reference numbers) within the filtered data.

Place this formula in the cell next to the period on Sheet A (e.g., next to 20250101 - 20250131): 

=COUNTA(UNIQUE(FILTER(SheetB!B:B, (SheetB!A:A>=LEFT(SheetA!A1,8)) * (SheetB!A:A<=RIGHT(SheetA!A1,8)) * (SheetB!C:C<>"Comms Review") * (SheetB!C:C<>"System Controls"), ""))) 

Explanation: 

• SheetA!A1: The cell on Sheet A with the period (e.g., "20250101 - 20250131"). LEFT(SheetA!A1,8) extracts the start date (20250101), and RIGHT(SheetA!A1,8) extracts the end date (20250131) by assuming a consistent format.
• SheetB!A:A: Column A on Sheet B with received dates.
• SheetB!B:B: Column B on Sheet B with reference numbers.
• SheetB!C:C: Column C on Sheet B with Enquiry Types.
• FILTER: Filters Sheet B data where:  
  • Dates in Column A fall between the period’s start and end dates.
  • Enquiry Types in Column C are not 'Comms Review' or 'System Controls'.
• UNIQUE: Extracts distinct reference numbers from the filtered Column B.
• COUNTA: Counts the number of non-blank distinct values.
• "": Returns an empty string if no data matches the filter.

Assumptions: 

• The period on Sheet A is formatted as "YYYYMMDD - YYYYMMDD" (e.g., "20250101 - 20250131").
• Dates in Sheet B Column A are in a recognizable date format (e.g., 2025/01/01 or 2025-01-01), which Excel can compare.
• Column references (A, B, C) match your data layout in Sheet B as shown in the image.

So here is the step to implement: 

Prepare Sheet A:  

• Ensure the period cell (e.g., A1) contains the date range (e.g., "20250101 - 20250131").
• Place the formula in the adjacent cell (e.g., B1).

Adjust Sheet B References:  

• Replace SheetB!A:A, SheetB!B:B, and SheetB!C:C with the actual range if your data doesn’t span the entire column (e.g., SheetB!A2:A1000 for a specific range).
• Ensure no headers are included in the range (e.g., start from row 2 if row 1 is a header).

Test the Formula:  

• Press Enter to calculate. The result should show the number of unique work items (e.g., 4 for the sample data in your image, excluding duplicates like 22101 and filtering out 'Comms Review').
• If you get an error (e.g., #VALUE!), check the date format or range references.

Handle Blanks and Errors:  

• If blanks cause issues, wrap the formula in IFERROR to return 0 for invalid periods

=IFERROR(COUNTA(UNIQUE(FILTER(SheetB!B:B, (SheetB!A:A>=LEFT(SheetA!A1,8)) * (SheetB!A:A<=RIGHT(SheetA!A1,8)) * (SheetB!C:C<>"Comms Review") * (SheetB!C:C<>"System Controls"), ""))), 0) 

If it’s doesn’t work: 

• Date Format Issue: If dates in Sheet B Column A aren’t comparable (e.g., text like "2025/01/01"), convert them to proper dates using Data > Text to Columns > Date or a helper column with DATEVALUE.
• Range Limitation: If using entire columns (A:A) slows performance, limit to your data range (e.g., A2:A1000).
• Alternative Approach: If the single formula fails, use a two-step process:

Create a helper column in Sheet B (e.g., D) with =IF(AND(A2>=LEFT(SheetA!A$1,8),A2<=RIGHT(SheetA!A$1,8),OR(C2<>"Comms Review",C2<>"System Controls")),B2,"").  

On Sheet A, use =COUNTA(UNIQUE(FILTER(SheetB!D:D,SheetB!D:D<>""))) to count distinct non-blank values. 

Thank you for your kindness and understanding. If you need anything else, please feel free to contact me. 

If this answer helps clarify the current situation, I would sincerely appreciate it if you could mark it as the answer. This will bring this information to the top of the feed, making it easier for other users to search for and reducing confusion for those who may be experiencing the same issue. 

Thanks and Have a good day!!! 

Best regards.      

Sting-Ng - Microsoft Community Support Specialist.

Answer 2

G2: =COUNT(UNIQUE(FILTER(B2:B6,(A2:A6>=E2)*(A2:A6<=F2))))

Answer 3

>... Is it possible to do it with one formula

Seems to me that for a single formula, it would be easier to just do them all at once for a couple of reasons.

Any other formulas could just use a simple lookup

Answer 4

Hi, Madeleen Bianco

If my answer helps, please mark it as an answer by click YES, it will definitely help others in the community with similar queries to find a solution to their problem faster. On top of that, it helps me have a good survey and makes me do my best. 

 
 
I appreciate it. Thank you for your support 

Sting-NG - Microsoft Community Support Specialist

Answer 5

In cell B2 of the linked-to workbook below:

=LET(dts,BYCOL(TRIM(TEXTSPLIT(A2, "-")), LAMBDA(a, DATE(LEFT(a,4), MID(a,5,2),RIGHT(a,2)))),st, TAKE(dts,,1),en, TAKE(dts,,-1),result, ROWS( UNIQUE( FILTER( SheetB!$B$2:$B$6,(SheetB!$A$2:$A$6<=en)*(SheetB!$A$2:$A$6>=st)*(SheetB!$C$2:$C$6<>"Comms Review")*(SheetB!$C$2:$C$6<>"System Controls")))),result)

It's a start only, because I've had to guess so much about what's in your sheets. Much better if you can supply a link to a workbook with this in because it takes my guesswork out and enables us to give a more definitive and relevant suggested solution.

Separately, on Sheet1, cell E4, a pivot table. This is so much easier than working out a formula.

The workbook: https://app.box.com/s/m89k1kmttljnhj1r7pywwppa7jepjz2h