Sumproduct not working

[Curtis]

I cannot seem to figure out why this formula below no loner works. It worked
prior to the following changes I have made

="1400" was changed to >=$b$4 (where the number 1400 was placed in that cell

<"1500 was changed to <$B$5 (where the number 1500 was placed in that cell)


Thanks
=SUMPRODUCT(('[YTDTB ACTUAL ''09-10.xls]YTDTB99'!$A$3:$A$277=$B11)*('[YTDTB
ACTUAL ''09-10.xls]YTDTB99'!$D$1:$CI$1>=B4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<B5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))+SUMPRODUCT(('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$A$3:$A$277=$A11)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1>=B4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<B5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))

 

[Pete_UK]

If you really had >="1400" (i.e. with the quotes) then you were
comparing a text value, whereas you have probably entered 1400 into B4
as a numeric value. You will need to enter it into B4 using an
apostrophe before the number, i.e. as '1400, to force it to be text
(or format the cell as Text and then type the number in). The same
applies to B5.

Hope this helps.

Pete

I cannot seem to figure out why this formula below no loner works. It worked
prior to the following changes I have made

="1400" was changed to >=$b$4 (where the number 1400 was placed in that cell

<"1500 was changed to <$B$5 (where the number 1500 was placed in that cell)

Thanks
=SUMPRODUCT(('[YTDTB ACTUAL ''09-10.xls]YTDTB99'!$A$3:$A$277=$B11)*('[YTDTB
ACTUAL ''09-10.xls]YTDTB99'!$D$1:$CI$1>=B4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<B5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))+SUMPRODUCT(('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$A$3:$A$277=$A11)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1>=B4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<B5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))

 

[Tom Hutchins]

="1400" is looking for text, not a numerical value. Likewise for "1500".
"1400" is not the same as 1400. If the formula worked when you specified the
text "1400" and "1500", then you need text entries in B4 and B5 now. Try
putting an apostrophe ' in front of the numbers in B4 and B5, to convert
them to text.

Hope this helps,

Hutch

I cannot seem to figure out why this formula below no loner works. It worked
prior to the following changes I have made

="1400" was changed to >=$b$4 (where the number 1400 was placed in that cell

<"1500 was changed to <$B$5 (where the number 1500 was placed in that cell)


Thanks
=SUMPRODUCT(('[YTDTB ACTUAL ''09-10.xls]YTDTB99'!$A$3:$A$277=$B11)*('[YTDTB
ACTUAL ''09-10.xls]YTDTB99'!$D$1:$CI$1>=B4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<B5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))+SUMPRODUCT(('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$A$3:$A$277=$A11)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1>=B4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<B5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))

 

[Jacob Skaria]

Try the below formula in the same worksheet or in the same workbook and then
add the xls file name....

=SUMPRODUCT((ISNUMBER(MATCH(YTDTB99!$A$3:$A$277,$A11:$B11,0)))*
(YTDTB99!$D$1:$CI$1>=B4)*(YTDTB99!$D$1:$CI$1<B5)*
(YTDTB99!$D$3:$CI$277))

If this post helps click Yes
---------------
Jacob Skaria

I cannot seem to figure out why this formula below no loner works. It worked
prior to the following changes I have made

="1400" was changed to >=$b$4 (where the number 1400 was placed in that cell

<"1500 was changed to <$B$5 (where the number 1500 was placed in that cell)


Thanks
=SUMPRODUCT(('[YTDTB ACTUAL ''09-10.xls]YTDTB99'!$A$3:$A$277=$B11)*('[YTDTB
ACTUAL ''09-10.xls]YTDTB99'!$D$1:$CI$1>=B4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<B5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))+SUMPRODUCT(('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$A$3:$A$277=$A11)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1>=B4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<B5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))