Count duplicate cell values per month
 

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

Count duplicate cell values per month
 

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

--
RyGuy


"Derek" wrote:

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

Count duplicate cell values per month
 

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


"ryguy7272" wrote in message
...
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---

--
RyGuy


"Derek" wrote:

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

Count duplicate cell values per month
 

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


--
Biff
Microsoft Excel MVP


"Derek" wrote in message
...
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

Count duplicate cell values per month
 

P.S.

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)))


--
Biff
Microsoft Excel MVP


"Derek" wrote in message
...
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

Count duplicate cell values per month
 

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)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
P.S.

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)))


--
Biff
Microsoft Excel MVP


"Derek" wrote in message
...
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

Count duplicate cell values per month
 

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

Derek

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
P.S.

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)))


--
Biff
Microsoft Excel MVP


"Derek" wrote in message
...
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

Count duplicate cell values per month
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Derek" wrote in message
...
Wow, thank you so much for your replies, they work just fine, thanks again

Derek

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
P.S.

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)))


--
Biff
Microsoft Excel MVP


"Derek" wrote in message
...
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