|
sumif or sumproduct?
I have a workbook with two active sheets. The second contains data and I
want to use the first for summary information. The data is basically dates (in column A) and numbers in the other columns. Based on some of the other questions and answers here, I wrote the formula: =SUMPRODUCT(((MONTH('Issue Detail'!$A3:$A275)=7)))*('Issue Detail'!$B3:$B275). Issue detail is the name of the second sheet; 7 is July; A3:A275 is the column of dates (formatted as 07/03/06, etc.); and B3:B275 is one of the columns of numbers. Although the formula seemingly has no errors, The only result I am getting is 0. I have been reading this forum for suggestions & guidance and, in advance, I want to say thanks for the past help. |
sumif or sumproduct?
Użytkownik "ronnomad" napisał w wiadomości ... I have a workbook with two active sheets. The second contains data and I want to use the first for summary information. The data is basically dates (in column A) and numbers in the other columns. Based on some of the other questions and answers here, I wrote the formula: =SUMPRODUCT(((MONTH('Issue Detail'!$A3:$A275)=7)))*('Issue Detail'!$B3:$B275). Issue detail is the name of the second sheet; 7 is July; A3:A275 is the column of dates (formatted as 07/03/06, etc.); and B3:B275 is one of the columns of numbers. Although the formula seemingly has no errors, The only result I am getting is 0. I have been reading this forum for suggestions & guidance and, in advance, I want to say thanks for the past help. u can use sumif function or array formula =sum(('Issue Detail'!$A3:$A275)=7)*('Issue Detail'!$B3:$B275)) remember to accept array formula with ctrl+shift+enter mcg |
sumif or sumproduct?
Ronnomad --
Not sure I can parse things right, but the first thing I observe is that a SUMPRODUCT formula has two arrays separated by a comma, and I don't see that in your formula. I suspect it's completing the SUMPRODUCT by multiplying by a nonexistent second array and getting zero, then multiplying against the second range. Try redoing the formula to put your column 'B' inside the SUMPRODUCT. HTH "ronnomad" wrote: I have a workbook with two active sheets. The second contains data and I want to use the first for summary information. The data is basically dates (in column A) and numbers in the other columns. Based on some of the other questions and answers here, I wrote the formula: =SUMPRODUCT(((MONTH('Issue Detail'!$A3:$A275)=7)))*('Issue Detail'!$B3:$B275). Issue detail is the name of the second sheet; 7 is July; A3:A275 is the column of dates (formatted as 07/03/06, etc.); and B3:B275 is one of the columns of numbers. Although the formula seemingly has no errors, The only result I am getting is 0. I have been reading this forum for suggestions & guidance and, in advance, I want to say thanks for the past help. |
sumif or sumproduct?
Things have moved on, See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "pdberger" wrote in message ... Ronnomad -- Not sure I can parse things right, but the first thing I observe is that a SUMPRODUCT formula has two arrays separated by a comma, and I don't see that in your formula. I suspect it's completing the SUMPRODUCT by multiplying by a nonexistent second array and getting zero, then multiplying against the second range. Try redoing the formula to put your column 'B' inside the SUMPRODUCT. HTH "ronnomad" wrote: I have a workbook with two active sheets. The second contains data and I want to use the first for summary information. The data is basically dates (in column A) and numbers in the other columns. Based on some of the other questions and answers here, I wrote the formula: =SUMPRODUCT(((MONTH('Issue Detail'!$A3:$A275)=7)))*('Issue Detail'!$B3:$B275). Issue detail is the name of the second sheet; 7 is July; A3:A275 is the column of dates (formatted as 07/03/06, etc.); and B3:B275 is one of the columns of numbers. Although the formula seemingly has no errors, The only result I am getting is 0. I have been reading this forum for suggestions & guidance and, in advance, I want to say thanks for the past help. |
sumif or sumproduct?
It's all down to the brackets. Your formula is being evaluated first as
SUMPRODUCT(((MONTH('Issue Detail'!$A3:$A275)=7))) which resolves to 0 because you have nothing to coerce the TRUE/FALSE values to 1/0. Even if you did, trying to multiply that SP result by the value array would give you #VALUE!. You can get past this by array-entering, but it would still give the wrong answer. SP should be a format like =SUMPRODUCT((condition1)[*(condition2)[*(condition3]]*(values)) which in your case would be =SUMPRODUCT((MONTH('Issue Detail'!$A3:$A275)=7)*('Issue Detail'!$B3:$B275)) or =SUMPRODUCT((MONTH('Issue Detail'!$A3:$A275)=7)*'Issue Detail'!$B3:$B275) as Don suggested. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "ronnomad" wrote in message ... I have a workbook with two active sheets. The second contains data and I want to use the first for summary information. The data is basically dates (in column A) and numbers in the other columns. Based on some of the other questions and answers here, I wrote the formula: =SUMPRODUCT(((MONTH('Issue Detail'!$A3:$A275)=7)))*('Issue Detail'!$B3:$B275). Issue detail is the name of the second sheet; 7 is July; A3:A275 is the column of dates (formatted as 07/03/06, etc.); and B3:B275 is one of the columns of numbers. Although the formula seemingly has no errors, The only result I am getting is 0. I have been reading this forum for suggestions & guidance and, in advance, I want to say thanks for the past help. |
| All times are GMT +1. The time now is 01:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com