Sumproduct & Numbers

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

I have a spreadsheet where I have 2 columns of numbers and am trying to use
SUMPRODUCT count paired cominations. Each person has a score Rating & Score
of 1, 2, 3 or 4

The database looks like this:
Name Rating Score
Fred Bloggs 1 2
Jo Evans 4 3
Anne Smtih 2 2
Jane Oliver 1 2
etc

The formula I am to count each of the 16 possible combinations is:

SUMPRODUCT(B1:B36=1)*SUMPRODUCT(C1:C36=1)
SUMPRODUCT(B1:B36=1)*SUMPRODUCT(C1:C36=1)
etc

However, I get a 0 return. I use the same formula on a database that uses
letters rather than numbers and this works fine, so I'm presuming it's
because this one uses numbers. I have tried formatting the cells for
general, text and numbers and nothing seems to change the result.

Any ideas?

Many thanks.
 
Your numbers may not be actual numbers. They may be text instead.

If you don't want to change your data to numbers, then you might try
something like this.
SUMPRODUCT(B1:B36*1=1)*SUMPRODUCT(C1:C36*1=1)
SUMPRODUCT(B1:B36*1=1)*SUMPRODUCT(C1:C36*1=1)

HTH,
Paul
 
Sorry, the forumula I'm using is without the second SUMPRODUCT

ie SUMPRODUCT(B1:B39=1)*SUMPRODUCT(C1:C39=1)
ETC

Sue
 
Oops! I didn't even pay attention to that.

=SUMPRODUCT((B1:B36*1=1)*(C1:C36*1=1))
=SUMPRODUCT((B1:B36*1=1)*(C1:C36*1=1))

or

=SUMPRODUCT(--(B1:B36*1=1),--(C1:C36*1=1))


HTH,
Paul


--
 
If you want to count the occasions where the same row has the same number
then use

=SUMPRODUCT(--(B1:B39=1),--(C1:C39=1))


Don't where you got the other formulas from


--


Regards,


Peo Sjoblom
 
Back
Top