Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct not working
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)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct not working
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 On Nov 3, 8:34*pm, Curtis wrote: 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)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct not working
="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 "Curtis" wrote: 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)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct not working
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,$A 11:$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 "Curtis" wrote: 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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT not working | Excel Worksheet Functions | |||
SUMPRODUCT Not Working | Excel Discussion (Misc queries) | |||
SUMPRODUCT not working | Excel Discussion (Misc queries) | |||
Sumproduct Not Working | Excel Worksheet Functions | |||
=SUMPRODUCT not working | Excel Worksheet Functions |