+ Reply to Thread
Results 1 to 2 of 2

COUNTIFS - But only count once for each like item

  1. #1
    Registered User
    Join Date
    06-15-2011
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Excel 2007
    Posts
    14

    COUNTIFS - But only count once for each like item

    Current formula:
    =COUNTIFS($H:$H,"Procedure",$B:$B,">="&$M$3,$B:$B,"<="&$N$3)

    Where:
    H:H = General format
    B:B = Date format
    M3 = Low Date
    N3 = High Date

    Basically, I am currently using this to report how many instances of the word "Procedure" occurs in column H for a date range (M3 - N3).

    However, I would like to remove the H:H, "Procedure" criteria and instead count by procedure form number stored in column F (general format).

    Any given procedure form number (ex: RSPW-8HZP7F) could be listed multiple times. I would like to increase the count by 1 only if the form number has not been counted before. So although a specific form number may be listed 5 times, I only want to count it once.

    Any thoughts on how to achieve this?

    Thanks!!
    Ryan

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: COUNTIFS - But only count once for each like item

    Hello Ryan,

    One possibility is to use a formula like this

    =SUM(IF(FREQUENCY(IF(B2:B100>=M3,IF(B2:B100<=N3,IF(F2:F100<>"",MATCH(F2:F100,F2:F100,0)))),ROW(F2:F100)-ROW(F2)+1),1))

    It's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

    You can extend the ranges but if you have a large amount of data you may find it slow
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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