Sumproduct & Numbers
 

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.


--

Sue

Sumproduct & Numbers
 

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


--

Sue

Sumproduct & Numbers
 

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

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

Sue


"Sue" wrote:

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.


--

Sue

Sumproduct & Numbers
 

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


--

"Sue" wrote in message
...
Sorry, the forumula I'm using is without the second SUMPRODUCT

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

Sue


"Sue" wrote:

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.


--

Sue

Sumproduct & Numbers
 

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



"Sue" wrote in message
...
Sorry, the forumula I'm using is without the second SUMPRODUCT

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

Sue


"Sue" wrote:

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.


--

Sue