Sumproduct & Numbers

[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.

 

[PCLIVE]

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

 

[Sue]

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

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

Sue

 

[PCLIVE]

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


--

 

[Peo Sjoblom]

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