Count Unique From Filtered Values

**diddydidit** · 07-11-2022, 08:00 PM

Hi.

I'm trying to create a report to count the number of events based on a few conditions. This is the formula I've came up with so far:

=SUM(COUNTIFS('Sheet1'!B:B,"202201",'Sheet1'!M:M,{"This is text 1","This is text 2","This is text 3","This is text 4"}))

The formula above works well counting the total number of events but it doesn't remove the duplicates in column C.

The first condition is to select the period 202201 in column B, select unique event numbers in column C, and then match these events with the text description in column M. Each event in column C has a unique number but it comes with many different asset numbers in column F, which end up duplicating these unique event numbers in column C. In other words, I only need to count the total number of unique events of each period based on the description and without the asset numbers.

This report is pulling data from another tab in the workbook which is also importing filtered data from a different spreadsheet. The goal is to allow the team to simply update the report clicking Refresh All.

Thank you in advance.

**FlameRetired** · 07-11-2022, 10:01 PM

diddydidit welcome to the forum.

Please see the instructions for uploading a workbook in your thread. It's in the 'gold' banner at the top of the page.

**diddydidit** · 07-13-2022, 07:28 PM

Sorry, I have now attached the workbook.

**FlameRetired** · 07-13-2022, 08:07 PM

Try these small modifications to your existing formula.

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 07-13-2022, 08:13 PM

Another way.

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 07-13-2022, 08:20 PM

And another way.

Formula:

Please Login or Register  to view this content.

**diddydidit** · 07-13-2022, 09:07 PM

Thank you! I tested all three formulas but it is counting the number of descriptions in column M, not column C, which is what I need. I played around changing the descriptions to see if the result calculated match the number of unique events in column C of Sample tab but it failed. For example, there are three types of description and the events occurred in period 202204 have all three of them as well as three unique event numbers in column C. If I change one of the descriptions in column M for any other to keep only two types of description, the formula changes the result from three to two. Hope it makes sense.

**FlameRetired** · 07-13-2022, 09:40 PM

I was/am confused.

The formula in your upload COUNTIFS formula had {"Notification completed","Notification failed","Campaign incomplete"} as the criteria. I assumed column C was a typo as those criteria are consistent with column M.

Try using this one.

Formula:

Please Login or Register  to view this content.

**diddydidit** · 07-13-2022, 09:49 PM

Perfect! Now it works fine. Thanks very much!

Does the column selection needs to be locked as in $B$2:$B$45? Since I'm actually importing the data from another workbook, I would like to be able to hit Refresh All whenever new data is imported, for example, for the upcoming months from August 22 to March 23. I'm asking because I removed the $ to test but the formula failed.

**FlameRetired** · 07-13-2022, 11:07 PM

There are 3 options that come immediately to mind.

1. Extend the ranges in the formula to 200-300 more rows than you think you will likely need. (Simple and easy, but may still require editing if you under estimate your needs.)

2. Change the range references to whole columns. Though you will never underestimate your needs, the speed of calculations will be noticeably slowed.

3. Create Dynamic Named Ranges. The ranges resize to fit the data automatically. This is my favorite. It is slightly tedious to set up, but once done you do not have to edit ranges in your formula and performance is not affected as with whole column references. Let me know if you want to do this. If you are unfamiliar with how to do these let me know. I can show you how to set them up.

**diddydidit** · 07-13-2022, 11:52 PM

I wouldn't mind trying the Dynamic Named Ranges. Thanks for that!

**diddydidit** · 07-25-2022, 10:52 PM

Sorry for reopening this thread. This formula works beautifully up to "This is text 4" but I'm having a problem with the formula below when I add one more text criteria in the end.

From this:

=SUM(SIGN(COUNTIFS('Sheet1'!$B$2:$B$2000,TEXT('Column Name A'!D2,"yyyymm"),'Column Name B'!$C$2:$C$2000,UNIQUE('Column Name B'!$C$2:$C$2000),'Column Name C'!$M$2:$M$2000,{"This is text 1","This is text 2","This is text 3","This is text 4"})))

To this:

=SUM(SIGN(COUNTIFS('Sheet1'!$B$2:$B$2000,TEXT('Column Name A'!D2,"yyyymm"),'Column Name B'!$C$2:$C$2000,UNIQUE('Column Name B'!$C$2:$C$2000),'Column Name C'!$M$2:$M$2000,{"This is text 1","This is text 2","This is text 3","This is text 4",">123 This is text 5"})))

The last criteria >123 This is text 5 drastically changes the result of the calculation that's beyond my understanding.

My guess is something to do with >123...

Thanks in advance.

Count Unique From Filtered Values

LinkBack

Thread Tools

Rate This Thread

Display

Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Re: Count Unique From Filtered Values

Thread Information

Users Browsing this Thread

Similar Threads

Count Unique values in a filtered list

[SOLVED] Count unique values between dates on filtered sheet

Count Unique values after being filtered

How to count unique values in filtered list?

count unique values in filtered column

Count Unique Values in a Filtered Column

Count Unique Values In A Filtered Row with Duplicates

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions