Another SUMPRODUCT Question

[enna49]

Hi
Not certain whether this can be done. What I am trying to do is count the
No of Accounts, the report could have many rows of the same Account No and I
only want to count it once. Would this be better done as a Pivot and link
back to that.

This is what I have
=SUMPRODUCT(--('DISPUTES REPORT'!$C$2:$C$2768=$B$3),--('DISPUTES
REPORT'!$D$2:$D$2768=$C$3),--('DISPUTES REPORT'!$E$2:$E$2768=$D$3)*('DISPUTES
REPORT'!$N$2:$N$2768>0))
Thank you in advance

 

[T. Valko]

You need to be more specific.

Do you want to count unique account numbers that meet certain conditions?
OK, so where are the account numbers and what are the conditions?

C2:C2768=B3, what's in C and what's in B3?
D22768=C3, what's in D and what's in C3?
E2:E2768=D3, what's in E and what's in D3?

N2:N2768>0, that one's self explanatory!

 

[enna49]

Hi

This is what is in the following

C2:C2768=B3, what's in C and what's in B3? Two Alpha chars
D22768=C3, what's in D and what's in C3? Two Alpha chars
E2:E2768=D3, what's in E and what's in D3? One or Two Alpha chars

The Account Nos are in N2:N2768

What is the code if I use this and Concatenate into a Col and use "begins
with"

Thanks

 

[T. Valko]

What is the code if I use this and Concatenate into

a Col and use "begins with"

I don't know what that means?

Let's try this...

Here's a small sample file that counts unique numbers that meet certain
conditions.

enna49.xls 14 kb

http://cjoint.com/?ceedE1jUoQ

Compare that sample file to what you're trying to do and we'll see if we can
hash out the differences.

 

[enna49]

Thank you for your help in this, but what I really require is the answer to
be 1. Maybe it is a big ask for Excel.

Thanks

 

[T. Valko]

what I really require is the answer to be 1.

Can you explain how you arrive at that result?