# Assistance with Unique values using a formula

1. ## Assistance with Unique values using a formula

Assistance with counting a unique number.

Col A is for IP Address
Col B is for severity (Low Medium High Critical))

I have the following code:
=SUM(IF(
({"Low","Medium","High","Critical"}=DataSheet!\$B\$1:\$B\$100),
1/COUNTIFS(
DataSheet!\$A\$1:\$A\$100,DataSheet!\$A\$1:\$A\$100,
DataSheet!\$B\$1:\$B\$100,{"Low","Medium","High","Critical"}
)),0)

This is searching column B for {"Low","Medium","High","Critical"} (not info) and returning with 14 unique IP Addresses from column A as the result which is incorrect. It should be 5 unique IP addresses.

When doing each severity at a time, it works. See below:

Low - 3 unique IP addresses
Medium - 3 unique IP addresses
High - 4 unique IP addresses
Critical - 4 unique IP addresses

But, when I do total, it returns with 14 because its just counting the total from each severity

The issue is, I only have 5 IP addresses total so the answer cant be 14. As a whole, this should return as 5 unique IP addresses.

I have attached an example worksheet.
Thank you.

2. ## Re: Assistance with Unique values using a formula

Try this in B7:

=SUMPRODUCT(1/COUNTIF(DataSheet!\$A\$1:\$A\$50,DataSheet!\$A\$1:\$A\$50))

3. ## Re: Assistance with Unique values using a formula

It works but column B has 5 different things (Low, Medium, High, Critical, Info).
I need it to not count INFO so thats where I'm stuck, This current code will count the infos.
Dave

4. ## Re: Assistance with Unique values using a formula

Anyone? I'm at a loss

5. ## Re: Assistance with Unique values using a formula

Hi,

That construction using SUM (or SUMPRODUCT) with a reciprocated COUNTIF(S) is horrendously slow to calculate over large ranges.

Try (array formula**):

=SUM(IF(FREQUENCY(IF(DataSheet!\$B\$1:\$B\$100={"Low","Medium","High","Critical"},MATCH(DataSheet!\$A\$1:\$A\$100,DataSheet!\$A\$1:\$A\$100,0)),ROW(DataSheet!\$A\$1:\$A\$100)-MIN(ROW(DataSheet!\$A\$1:\$A\$100))+1),1))

Regards

6. ## Re: Assistance with Unique values using a formula

XOR LX it works. I don't know how you did it but I've been struggling with this for hours.

Out of curiosity, How would I add one more criteria into this formula?

So using the same info as before but say Column C had three different manufacturers in it like "Apple" "Windows" "Linux"
And I wanted to start this code off by saying if contains Apple or Windows, then proceed with this code?
So it would index column C somehow to verify the criteria is met before proceeding?

Thanks

7. ## Re: Assistance with Unique values using a formula

Actually the version of the solution I gave you, although perfectly sound, is perhaps not the best, in the sense that it is not easily extendible to allow further ranges with criteria to be incorporated. I probably should have posted this version:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(DataSheet!\$B\$1:\$B\$100,{"Low","Medium","High","Critical"},0)),MATCH(DataSheet!\$A\$1:\$A\$100,DataSheet!\$A\$1:\$A\$100,0)),ROW(DataSheet!\$A\$1:\$A\$100)-MIN(ROW(DataSheet!\$A\$1:\$A\$100))+1),1))

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(DataSheet!\$B\$1:\$B\$100,{"Low","Medium","High","Critical"},0)),IF(ISNUMBER(MATCH(DataSheet!\$C\$1:\$C\$50,{"Apple","Windows"},0)),MATCH(DataSheet!\$A\$1:\$A\$100,DataSheet!\$A\$1:\$A\$100,0))),ROW(DataSheet!\$A\$1:\$A\$100)-MIN(ROW(DataSheet!\$A\$1:\$A\$100))+1),1))

Here you can hopefully see that we can add as many further criteria for other columns as we wish, the generic required inclusion being of the form:

=IF(ISNUMBER(MATCH(Some_Range,Some_Criteria,0))

Note that Some_Criteria does not have to be hard-coded within the formula, but can also comprise an actual worksheet range containing those criteria.

Regards

8. ## Re: Assistance with Unique values using a formula

Thank you very much! I would have never figured this one out. Your understanding of excel is Amazing. Thank you!

9. ## Re: Assistance with Unique values using a formula

You're welcome!

Cheers

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1