Count duplicate cell values per month

[Derek]

Hi experts

I need to look down column Q, R, S and T and find duplicate values for the
same month and in a new cell just put a number of times they are duplicated
on the same row

ill explain, i.e.

Q R S T
Part 1 Part 2 Part3 Part 4
30.00 30.00 0.00 0.00

So in rows Q - T 30.00 appers twice, so id like cell U e.g. to report the
number 2

I hope that makes sense

Thanks

Derek

 

[ryguy7272]

This will count duplicates for you:
=SUMPRODUCT((A2:A78<>"")/COUNTIF(A2:A78,A2:A78&"")*(A2:A78<>""))
(starts counting in row 2; assumes title or label is in row 1)


=COUNT(1/FREQUENCY(A1:A400,A1:A400))
(does same as function above)


=SUM(IF(A2:A400<>"",1/COUNTIF(A2:A400,A2:A400)))
(does same as function above; must be entered with Ctrl+Shift+Enter...not
just enter)



Regards,
Ryan---

 

[Peo Sjoblom]

No need for checking the blanks twice

=SUMPRODUCT((A2:A78<>"")/COUNTIF(A2:A78,A2:A78&""))

will suffice


don't know if that's what the OP is asking though. He probably needs to
explain a bit more


--


Regards,


Peo Sjoblom

 

[T. Valko]

So, I guess that means you want to exclude 0s?

What result do you want if no value is duplicated?

This array formula** will return a blank if there are no duplicates
(excludes 0s):

=IF(MAX(FREQUENCY(IF(Q2:T2<>0,Q2:T2),Q2:T2))<2,"",MAX(FREQUENCY(IF(Q2:T2<>0,Q2:T2),Q2:T2)))

 

[T. Valko]

P.S.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

Improvement:

Slightly shorter. Still array entered** :

=LOOKUP(MAX(FREQUENCY(IF(Q2:T2<>0,Q2:T2),Q2:T2)),{0;2;3;4},{"";2;3;4})

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Derek]

Wow, thank you so much for your replies, they work just fine, thanks again

Derek

 

[T. Valko]

You're welcome. Thanks for the feedback!