sumif or sumproduct?

[Guest]

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.

 

[Gazeta]

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

 

[Don Guillett]

=SUMPRODUCT(((MONTH('Issue Detail'!$A3:$A275)=7)))*('Issue
Detail'!$B3:$B275)
try it this way. Are your dates really dates?
=SUMPRODUCT((MONTH('Issue Detail'!$A3:$A275)=7)*'Issue Detail'!$B3:$B275)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)