+ Reply to Thread
Results 1 to 9 of 9

Assistance with Unique values using a formula

  1. #1
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    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.

    HELP Please, I'm stuck!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    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. #3
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    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. #4
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Re: Assistance with Unique values using a formula

    Anyone? I'm at a loss

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    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. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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))

    which is readily extendible, viz:

    =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. #8
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    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. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Assistance with Unique values using a formula

    You're welcome!

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 04-11-2017, 08:00 AM
  2. Replies: 11
    Last Post: 10-27-2016, 07:33 PM
  3. [SOLVED] Formula or VBA - Comparison CSV Values between 3 cells for used range. Find unique values.
    By grphillips in forum Excel Programming / VBA / Macros
    Replies: 36
    Last Post: 06-09-2014, 02:12 PM
  4. Assistance in Sum/CountA formula to only count 1 of duplicate values
    By Excelhandicap in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2013, 09:06 PM
  5. Replies: 9
    Last Post: 07-13-2011, 09:59 AM
  6. Replies: 8
    Last Post: 12-30-2008, 12:06 PM

Bookmarks

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