Couting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to count two things, I want to count the number of order ID's that
have an assoicated Affilaite ID's (basically where column A and column B are
not blank) And I want to count the number of order ID's that have no
assoicated affiliate ID (Basically where column A is not Blank and Column B
is blank. I know I can use count, and countblank, but I need a formula that
can be repasted to other sheets with the same data set up...In other words, I
will not know how many Order ID's are in column A. That is to say, I
woulnd't know the range I would use for the Countblank formula without
readjusting it eveytime I pasted it over to a new sheet.
(my sheet also has many more rows in it, but that doesn't really matter) I
was thinking the sumproduct formula would work, but I just can't figure it
out. Any help would be appreciated. Thanks in advance.

Colum A Column B

Order Affilaite ID's
103782 4203
103781 17
103780 4203
103779 4203
103778 4641
103777 4203
103776
103775 4216
103774 CD121
103773 4203
103772 4216
103771 4203
103770 4785
103769
103768
103767 CD121
103766
103765 4046
103764
103763
 
Hi Matt!

If col A and col B only contain the type of data in your
example, you really don't need to know the size of the
range. Just use the entire column as the reference. To
count the number order ID's that have an assoicated
Affilaite ID:

=COUNTA(B:B)

To count the number of order ID's that have no assoicated
affiliate ID:

=COUNTA(A:A)-COUNTA(B:B)

Or, am I way off base on this?

Biff
 
Hi
try
1. A and B not blank:
=SUMPRODUCT(--(A1:A10000<>""),--(B1:B10000<>""))

2. A not blank and B blank:
=SUMPRODUCT(--(A1:A10000<>""),--(B1:B10000=""))
or
=SUMPRODUCT(--(A1:A10000<>""),--ISBLANK(B1:B10000))
 
Hi Biff
If col A and col B only contain the type of data in your
example, you really don't need to know the size of the
range. Just use the entire column as the reference. To
count the number order ID's that have an assoicated
Affilaite ID:

=COUNTA(B:B)
Click to expand...

This would return wrong results if column A could be empty then column
B contains a value. But this depends of course on the OP's data :-)

To count the number of order ID's that have no assoicated
affiliate ID:

=COUNTA(A:A)-COUNTA(B:B)
Click to expand...

same problem as above. so better to use a SUMPRODUCT formula with two
conditions.

Frank
 
Back
Top