| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sumproduct
I have a worksheet with data in the region C1:Z10.
I want to sum over everything in the region C1:Z10 which meets certain criteria for columns A & B (more specifically: equal to 1). Also, I don't want to create an extra column of sums for the region. All I want is a formula. I know I can use SumProduct((A1:A10=1)*(B1:B10=1), C1:C10 + D1:D10 +...) , but that's too clumsy. What can I do? Suggestions are most welcome! |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sumproduct
=SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(C1:Z10))
-- __________________________________ HTH Bob "robot" wrote in message ... I have a worksheet with data in the region C1:Z10. I want to sum over everything in the region C1:Z10 which meets certain criteria for columns A & B (more specifically: equal to 1). Also, I don't want to create an extra column of sums for the region. All I want is a formula. I know I can use SumProduct((A1:A10=1)*(B1:B10=1), C1:C10 + D1:D10 +...) , but that's too clumsy. What can I do? Suggestions are most welcome! |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sumproduct
Hi,
I'm not sure I fully understand but maybe this =SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(C1:Z10)) Mike "robot" wrote: I have a worksheet with data in the region C1:Z10. I want to sum over everything in the region C1:Z10 which meets certain criteria for columns A & B (more specifically: equal to 1). Also, I don't want to create an extra column of sums for the region. All I want is a formula. I know I can use SumProduct((A1:A10=1)*(B1:B10=1), C1:C10 + D1:D10 +...) , but that's too clumsy. What can I do? Suggestions are most welcome! |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sumproduct
The formula works! Thank you both for your replies.
However, some of the cells in the region C1:Z10 include text but not numbers. I would like to treat them as zeros, so I tried =SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(IF(ISNUMBER(C1: Z10), C1:Z10, 0)) However, the formula always returns zero. What can I do? "Mike H" ... Hi, I'm not sure I fully understand but maybe this =SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(C1:Z10)) Mike "robot" wrote: I have a worksheet with data in the region C1:Z10. I want to sum over everything in the region C1:Z10 which meets certain criteria for columns A & B (more specifically: equal to 1). Also, I don't want to create an extra column of sums for the region. All I want is a formula. I know I can use SumProduct((A1:A10=1)*(B1:B10=1), C1:C10 + D1:D10 +...) , but that's too clumsy. What can I do? Suggestions are most welcome! |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sumproduct
=SUM(IF(ISNUMBER(C1:Z10),IF((A1:A10=1)*(B1:B10=1), C1:Z10,0)))
as an array formula, commit with Ctrl-Shift-Enter -- __________________________________ HTH Bob "robot" wrote in message ... The formula works! Thank you both for your replies. However, some of the cells in the region C1:Z10 include text but not numbers. I would like to treat them as zeros, so I tried =SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(IF(ISNUMBER(C1: Z10), C1:Z10, 0)) However, the formula always returns zero. What can I do? "Mike H" ... Hi, I'm not sure I fully understand but maybe this =SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(C1:Z10)) Mike "robot" wrote: I have a worksheet with data in the region C1:Z10. I want to sum over everything in the region C1:Z10 which meets certain criteria for columns A & B (more specifically: equal to 1). Also, I don't want to create an extra column of sums for the region. All I want is a formula. I know I can use SumProduct((A1:A10=1)*(B1:B10=1), C1:C10 + D1:D10 +...) , but that's too clumsy. What can I do? Suggestions are most welcome! |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sumproduct
Thank you Bob, you are a great help.
While your formula works, I am still a little uncomfortable about the use of arrays of different dimensions (A1:A10 and C1:Z10) in a single array formula. It is because according to the Excel XP help file, all arrays in an array formula should have the same dimensions. "Bob Phillips" bl... =SUM(IF(ISNUMBER(C1:Z10),IF((A1:A10=1)*(B1:B10=1), C1:Z10,0))) as an array formula, commit with Ctrl-Shift-Enter -- __________________________________ HTH Bob "robot" wrote in message ... The formula works! Thank you both for your replies. However, some of the cells in the region C1:Z10 include text but not numbers. I would like to treat them as zeros, so I tried =SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(IF(ISNUMBER(C1: Z10), C1:Z10, 0)) However, the formula always returns zero. What can I do? "Mike H" ... Hi, I'm not sure I fully understand but maybe this =SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(C1:Z10)) Mike "robot" wrote: I have a worksheet with data in the region C1:Z10. I want to sum over everything in the region C1:Z10 which meets certain criteria for columns A & B (more specifically: equal to 1). Also, I don't want to create an extra column of sums for the region. All I want is a formula. I know I can use SumProduct((A1:A10=1)*(B1:B10=1), C1:C10 + D1:D10 +...) , but that's too clumsy. What can I do? Suggestions are most welcome! |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sumproduct
Yet you were quite happy to use it with SUMPRODUCT, which is effectively an
array formula, and the help makes the same statement about its dimensions, albeit in different words. -- __________________________________ HTH Bob "robot" wrote in message ... Thank you Bob, you are a great help. While your formula works, I am still a little uncomfortable about the use of arrays of different dimensions (A1:A10 and C1:Z10) in a single array formula. It is because according to the Excel XP help file, all arrays in an array formula should have the same dimensions. "Bob Phillips" bl... =SUM(IF(ISNUMBER(C1:Z10),IF((A1:A10=1)*(B1:B10=1), C1:Z10,0))) as an array formula, commit with Ctrl-Shift-Enter -- __________________________________ HTH Bob "robot" wrote in message ... The formula works! Thank you both for your replies. However, some of the cells in the region C1:Z10 include text but not numbers. I would like to treat them as zeros, so I tried =SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(IF(ISNUMBER(C1: Z10), C1:Z10, 0)) However, the formula always returns zero. What can I do? "Mike H" ... Hi, I'm not sure I fully understand but maybe this =SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(C1:Z10)) Mike "robot" wrote: I have a worksheet with data in the region C1:Z10. I want to sum over everything in the region C1:Z10 which meets certain criteria for columns A & B (more specifically: equal to 1). Also, I don't want to create an extra column of sums for the region. All I want is a formula. I know I can use SumProduct((A1:A10=1)*(B1:B10=1), C1:C10 + D1:D10 +...) , but that's too clumsy. What can I do? Suggestions are most welcome! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| SUMPRODUCT except for | New Users to Excel | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| Sumproduct | Excel Worksheet Functions | |||
| Sumproduct | Excel Worksheet Functions | |||
Hybrid Mode
