| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with text and numbers in cells | New Users to Excel | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| Sumproduct Numbers and Text? | New Users to Excel | |||
| SUMPRODUCT USING WORDS AS WELL AS NUMBERS FOR CRITERIA | Excel Worksheet Functions | |||
| Sumproduct not working when summing values between two numbers | Excel Worksheet Functions | |||
Linear Mode
