COUNTIFS with no duplicates

How to modify this following formula so duplicates are counted once?

=COUNTIFS(Assignment,">"&0,plant,"="&C54,posting_date,">="&'Months & Dates'!$C$7,posting_date,"<="&'Months & Dates'!$D$7)

Thanks for your help.

It's difficult to place in context without a work example.

What are you trying to do with Count, if not count the duplicates? Are you trying to exclude duplicate rows that have all the same data?

I am trying to exclude the duplicate data.

Thanks

Originally Posted by daffodil11

It's difficult to place in context without a work example.

What are you trying to do with Count, if not count the duplicates? Are you trying to exclude duplicate rows that have all the same data?

I am providing a sample data, so you can better help me out.

On the tab "Cal", the number of assignments (which are shown on the tab "Data") needs to be counted (calculated).
Multiple occurrences should be considered once. The posting date should be taken into consideration. "61 to 90 days" refers to the month of May (more info on the 1st tab).

Please let me know if you have any questions.

Thanks


sample_data.xlsx

Tony,

Please consider the month of May for the calculation. The number of assignment is 1 for each plant (DFSA, DSEC and STDS) based on my manual calculation.

See the files bro, give me a feedback if its meet your expectation...

Azumi

Azumi,

You are close to the correct formula. I think FREQUENCY needs to be used to avoid counting the duplicates. Remember that the assignments should be counted once.

Thanks

Tony,

Your proposed formula didn’t work.

Thank you for the attempt.

Originally Posted by Tony Valko

Here's my best guess...

You want to count the unique plants that meet the conditions.

Try this array formula**:

=SUM(IF(FREQUENCY(IF(Assignment>0,IF(Plant=C54,IF(Posting_Date>='Months & Dates'!$C$7,IF(Posting_Date<='Months & Dates'!$D$7,MATCH(Plant,Plant,0))))),ROW(Plant)-MIN(ROW(Plant))+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Ok maybe this, with little modification,

=IF(SUMPRODUCT(--(Data!$C$5:$C$16<=$F$5),--(Data!$C$5:$C$16>=Cal!$E$5);--(Data!$D$5:$D$16=Cal!C7))>=1,1)

Same file, other alternative formula (Array Formula):

=SUM(--(FREQUENCY(IF((Data!$D$5:$D$16=Cal!C7)*(Data!$C$5:$C$16>=Cal!$E$5)*(Data!$C$5:$C$16<=Cal!$F$5),MATCH(Data!$E$5:$E$16,Data!$E$5:$E$16)),ROW(Data!$E$5:$E$16)-ROW(Data!$E$5)+1)>0))

then Hit CTRL-SHIFT-ENTER Button Same time....
Azumi