Count non duplicates while using countifs critera

[jimdac]

Hi All,
I need to count the number of unique ID numbers after I use the countifs statement. I have multiple criteria to sort out the data which produces the ID numbers but there are duplicates numbers and then I need to get the number of Unique ID numbers from that criteria data

What i have seen in the forums so far, usually deals with countif

Just so you get what I am looking at
Column D is The depot names, "SYD"
G column is a text field with that contains the words in S3. i.e "DAFF"
C column is date range between 2 sets of dates $L1 and $M1

this produces a set of data that contains multiple ID numbers that are in column A

This is the formula I thought would help

{=SUMPRODUCT(1/COUNTIFS(D2:D22339,"Syd",G2:G22339,S3,C2:C22339,">="&L1,C2:C22339,"<="&M1))}
but it is giving me 0.000325 response.

I feel that I need to put in column A range somewhere but unsure??

thanks for helping out in advance
Jim

 

[Aladin Akyurek]

What does "contains the words in S3" mean? Are you not just wanting to count unique items in column G, while column D = SYD and column C is between L1 and M1?

 

[jimdac]

Hi Aladin,
Sorry I should have stated that The ID numbers are in Col A

One of the criteria is to find all rows that have SYD in Col D, the works "DAFF" in col G and between the dates of LI and MI.
The Col G may contain different words. Worming or Vaccinations, or Vetwork
So 1 ID number could have a row with DAFF next row with Vaccination, etc

Hope this helps

Jim

 

[Aladin Akyurek]

Hi Aladin,
Sorry I should have stated that The ID numbers are in Col A

One of the criteria is to find all rows that have SYD in Col D, the works "DAFF" in col G and between the dates of LI and MI.
The Col G may contain different words. Worming or Vaccinations, or Vetwork
So 1 ID number could have a row with DAFF next row with Vaccination, etc

Hope this helps

Jim

So we are doing a distinct count involving the ID's in A under the following conditions:

D = SYD
G = DAF or contains DAFF (which: equal or contains?)
C is between L1 and M1

 

[Aladin Akyurek]

Whichever you intend to have...

1. (G = DAFF) Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$400=""),IF($D$2:$D$400="SYD",IF($G$2:$G$400="DAFF",IF($G$2:$G$400>=$L1,IF($G$2:$G$400<=$M1,MATCH($A$2:$A$400,$A$2:$A$400,0)))))),ROW($A$2:$A$400)-ROW($A$2)+1),1))

2. (G contains DAFF) Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$400=""),IF($D$2:$D$400="SYD",IF(ISNUMBER(SEARCH("DAFF",$G$2:$G$400)),IF($G$2:$G$400>=$L1,IF($G$2:$G$400<=$M1,MATCH($A$2:$A$400,$A$2:$A$400,0)))))),ROW($A$2:$A$400)-ROW($A$2)+1),1))

 

[jimdac]

Hi Aladin,
I have changed it slightly but all seems to work well
DAFF is not the entire word so I changed that to a cell reference that contain the entire text
L1 and M1 refer to Col C

Again thank you for your help on this it was much appreciated
Can I ask what the last part does
MATCH($A$2:$A$400,$A$2:$A$400,0)))))),ROW($A$2:$A$400)-ROW($A$2)+1),1))

Cheers again

 

[Aladin Akyurek]

Hi Aladin,
I have changed it slightly but all seems to work well
DAFF is not the entire word so I changed that to a cell reference that contain the entire text
L1 and M1 refer to Col C

Again thank you for your help on this it was much appreciated
Can I ask what the last part does
MATCH($A$2:$A$400,$A$2:$A$400,0)))))),ROW($A$2:$A$400)-ROW($A$2)+1),1))

Cheers again

Yes, the date range should be a range in C. So, you opted for the 2nd "contains" option, right?

See for an explanation of the FREQUENCY formula: https://www.mrexcel.com/forum/excel...ing-sum-if-frequency-match-2.html#post3156949 [By the way: range<>"" is the same as 1-(range="").]