Another SUMPRODUCT Question

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$27680))
Thank you in advance

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:N27680, that one's self explanatory!

--
Biff
Microsoft Excel MVP


"enna49" wrote in message
...
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$27680))
Thank you in advance

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" wrote:

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:N27680, that one's self explanatory!

--
Biff
Microsoft Excel MVP


"enna49" wrote in message
...
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$27680))
Thank you in advance


.

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.

--
Biff
Microsoft Excel MVP


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

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:N27680, that one's self explanatory!

--
Biff
Microsoft Excel MVP


"enna49" wrote in message
...
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$27680))
Thank you in advance


.

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" wrote:

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.

--
Biff
Microsoft Excel MVP


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

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:N27680, that one's self explanatory!

--
Biff
Microsoft Excel MVP


"enna49" wrote in message
...
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$27680))
Thank you in advance


.



.

what I really require is the answer to be 1.

Can you explain how you arrive at that result?

--
Biff
Microsoft Excel MVP


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

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.

--
Biff
Microsoft Excel MVP


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

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:N27680, that one's self explanatory!

--
Biff
Microsoft Excel MVP


"enna49" wrote in message
...
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$27680))
Thank you in advance


.



.