|
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! |
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! |
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! |
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! |
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! |
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! |
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! |
Sumproduct
I'll try getting used to this new concept. Thanks again, Bob.
"Bob Phillips" bl... 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! |
| All times are GMT +1. The time now is 11:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com