SUMPRODUCT Unique Values
 

I'm trying to combine a series of conditions while ignoring duplicates to get a result. In the below data, I need to find Nicholson, Jack in column B and count all the "yes" values in columns C & D, but ignore count for duplicates in column A.

---- DATA ----
A B C D
Week 1 Nicholson, Jack yes no
Week 3 Nicholson, Jack yes yes
Week 4 Nicholson, Jack yes yes
Week 5 Nicholson, Jack yes yes
Week 6 Nicholson, Jack yes yes
Week 7 Nicholson, Jack yes yes
Week 8 Nicholson, Jack yes yes
Week 8 Nicholson, Jack yes yes
Week 8 Washington, Denzel yes yes

Seeking Result: 13

Thank you so much in advance!

SUMPRODUCT Unique Values
 

Hi,

Am Mon, 20 Nov 2017 11:02:27 -0800 (PST) schrieb
:

I'm trying to combine a series of conditions while ignoring duplicates to get a result. In the below data, I need to find Nicholson, Jack in column B and count all the "yes" values in columns C & D, but ignore count for duplicates in column A.

---- DATA ----
A B C D
Week 1 Nicholson, Jack yes no
Week 3 Nicholson, Jack yes yes
Week 4 Nicholson, Jack yes yes
Week 5 Nicholson, Jack yes yes
Week 6 Nicholson, Jack yes yes
Week 7 Nicholson, Jack yes yes
Week 8 Nicholson, Jack yes yes
Week 8 Nicholson, Jack yes yes
Week 8 Washington, Denzel yes yes

Seeking Result: 13

try:
=SUMPRODUCT((MATCH(A1:A9,A1:A9,0)=ROW(1:9))*(B1:B9 ="Nicholson, Jack")*(C1:D9="yes"))


Regards
Claus B.
--
Windows10
Office 2016

SUMPRODUCT Unique Values
 

On Monday, November 20, 2017 at 2:12:59 PM UTC-5, Claus Busch wrote:
Hi,

Am Mon, 20 Nov 2017 11:02:27 -0800 (PST) schrieb

I'm trying to combine a series of conditions while ignoring duplicates to get a result. In the below data, I need to find Nicholson, Jack in column B and count all the "yes" values in columns C & D, but ignore count for duplicates in column A.

---- DATA ----
A B C D
Week 1 Nicholson, Jack yes no
Week 3 Nicholson, Jack yes yes
Week 4 Nicholson, Jack yes yes
Week 5 Nicholson, Jack yes yes
Week 6 Nicholson, Jack yes yes
Week 7 Nicholson, Jack yes yes
Week 8 Nicholson, Jack yes yes
Week 8 Nicholson, Jack yes yes
Week 8 Washington, Denzel yes yes

Seeking Result: 13

try:
=SUMPRODUCT((MATCH(A1:A9,A1:A9,0)=ROW(1:9))*(B1:B9 ="Nicholson, Jack")*(C1:D9="yes"))


Regards
Claus B.
--
Windows10
Office 2016

Claus,
Thanks for getting back so quick. Unfortunately, you the formula you provided just results in 0.

Best regards,
Chris

SUMPRODUCT Unique Values
 

On Monday, November 20, 2017 at 2:12:59 PM UTC-5, Claus Busch wrote:
Hi,

Am Mon, 20 Nov 2017 11:02:27 -0800 (PST) schrieb

I'm trying to combine a series of conditions while ignoring duplicates to get a result. In the below data, I need to find Nicholson, Jack in column B and count all the "yes" values in columns C & D, but ignore count for duplicates in column A.

---- DATA ----
A B C D
Week 1 Nicholson, Jack yes no
Week 3 Nicholson, Jack yes yes
Week 4 Nicholson, Jack yes yes
Week 5 Nicholson, Jack yes yes
Week 6 Nicholson, Jack yes yes
Week 7 Nicholson, Jack yes yes
Week 8 Nicholson, Jack yes yes
Week 8 Nicholson, Jack yes yes
Week 8 Washington, Denzel yes yes

Seeking Result: 13

try:
=SUMPRODUCT((MATCH(A1:A9,A1:A9,0)=ROW(1:9))*(B1:B9 ="Nicholson, Jack")*(C1:D9="yes"))


Regards
Claus B.
--
Windows10
Office 2016

I think I've diagnosed further. If my data has column headers and I move the data down, it will mess up the formula