Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT #VALUE! problem
This should be simple, but its defeating me.
Column A is a date, Column C is text, Column F is a number, and column G is =IF(C10="Sales",F10/9,"") In a separate worksheet, a SUMPRODUCT formula works fine to add up the values in Column F where they fall between two dates (entered in D2 and G2), i.e: =SUMPRODUCT(--('Sales&Income'!$F$10:$F$24),--('Sales&Income'!$A$10:$A$24=($D$2)),--('Sales&Income'!A10:A24<=($G$2))) I also want to sum the aforementioned Column G for the same date parameters, but =SUMPRODUCT(--('Sales&Income'!$G$10:$G$24),--('Sales&Income'!$A$10:$A$24=($D$2)),--('Sales&Income'!A10:A24<=($G$2))) returns #VALUE! I suspect it is doing this because it doesnt like the €˜value if false result ("") of the =IF(C10="Sales",F10/9,"") formula. Is there a way to get around it? Many thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT #VALUE! problem
=SUM(IF(('Sales&Income'!$G$10:$G$24<"")*('Sales&I ncome'!$A$10:$A$24=$D$2)*('Sales&Income'!A10:A24< =$G$2),('Sales&Income'!$G$10:$G$24)))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Vibeke" wrote in message ... This should be simple, but it's defeating me. Column A is a date, Column C is text, Column F is a number, and column G is =IF(C10="Sales",F10/9,"") In a separate worksheet, a SUMPRODUCT formula works fine to add up the values in Column F where they fall between two dates (entered in D2 and G2), i.e: =SUMPRODUCT(--('Sales&Income'!$F$10:$F$24),--('Sales&Income'!$A$10:$A$24=($D$2)),--('Sales&Income'!A10:A24<=($G$2))) I also want to sum the aforementioned Column G for the same date parameters, but =SUMPRODUCT(--('Sales&Income'!$G$10:$G$24),--('Sales&Income'!$A$10:$A$24=($D$2)),--('Sales&Income'!A10:A24<=($G$2))) returns #VALUE! I suspect it is doing this because it doesn't like the 'value if false' result ("") of the =IF(C10="Sales",F10/9,"") formula. Is there a way to get around it? Many thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT #VALUE! problem
Change the formula to
=SUMPRODUCT('Sales&Income'!$G$10:$G$24,--('Sales&Income'!$A$10:$A$24=($D$2)),--('Sales&Income'!A10:A24<=($G$2))) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Vibeke" wrote in message ... This should be simple, but it's defeating me. Column A is a date, Column C is text, Column F is a number, and column G is =IF(C10="Sales",F10/9,"") In a separate worksheet, a SUMPRODUCT formula works fine to add up the values in Column F where they fall between two dates (entered in D2 and G2), i.e: =SUMPRODUCT(--('Sales&Income'!$F$10:$F$24),--('Sales&Income'!$A$10:$A$24=($D$2)),--('Sales&Income'!A10:A24<=($G$2))) I also want to sum the aforementioned Column G for the same date parameters, but =SUMPRODUCT(--('Sales&Income'!$G$10:$G$24),--('Sales&Income'!$A$10:$A$24=($D$2)),--('Sales&Income'!A10:A24<=($G$2))) returns #VALUE! I suspect it is doing this because it doesn't like the 'value if false' result ("") of the =IF(C10="Sales",F10/9,"") formula. Is there a way to get around it? Many thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT #VALUE! problem
Vibeke wrote...
This should be simple, but it's defeating me. Column A is a date, Column C is text, Column F is a number, and column G is =IF(C10="Sales",F10/9,"") In a separate worksheet, a SUMPRODUCT formula works fine to add up the values in Column F where they fall between two dates (entered in D2 and G2), i.e: =SUMPRODUCT(--('Sales&Income'!$F$10:$F$24), --('Sales&Income'!$A$10:$A$24=($D$2)), --('Sales&Income'!A10:A24<=($G$2))) You don't need to (AND SHOULDN'T!) use -- on the col F range. If they're numbers or blank cells, you can sum them directly. So try =SUMPRODUCT(--('Sales&Income'!$A$10:$A$24=$D$2), --('Sales&Income'!A10:A24<=$G$2),'Sales&Income'!$F$1 0:$F$24) I also want to sum the aforementioned Column G for the same date parameters, but =SUMPRODUCT(--('Sales&Income'!$G$10:$G$24), --('Sales&Income'!$A$10:$A$24=($D$2)), --('Sales&Income'!A10:A24<=($G$2))) returns #VALUE! I suspect it is doing this because it doesn't like the 'value if false' result ("") of the =IF(C10="Sales",F10/9,"") formula. . . . Correct diagnosis. Use the same sort of formula as I proposed above for column F. =SUMPRODUCT(--('Sales&Income'!$A$10:$A$24=$D$2), --('Sales&Income'!A10:A24<=$G$2),'Sales&Income'!$G$1 0:$G$24) When ranges or arrays appear as separate arguments WITHOUT --s or any other arithmetic operators, SUMPRODUCT ignores text values in the range/array. For example, =SUMPRODUCT({1;"";3;4;"x"}) returns 8, while =SUMPRODUCT(--{1;"";3;4;"x"}) returns #VALUE!. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT #VALUE! problem
Thank you all for your suggestions. In the intervening period between
posting the question & discovering your replies, I found an error in my thinking, and a solution to my problem, namely that I could use in Column G =IF(OR(C15="Sales",C15="Sundry"),B15/9) And then use two seperate SUMPRODUCTS for "sales' and Sundry" , and simply add the results together. A bit clunky, but it works. Now, armed with your suggestiosn, I will go back and make it a bit tider. As always, thank you for your help. "Harlan Grove" wrote: Vibeke wrote... This should be simple, but it's defeating me. Column A is a date, Column C is text, Column F is a number, and column G is =IF(C10="Sales",F10/9,"") In a separate worksheet, a SUMPRODUCT formula works fine to add up the values in Column F where they fall between two dates (entered in D2 and G2), i.e: =SUMPRODUCT(--('Sales&Income'!$F$10:$F$24), --('Sales&Income'!$A$10:$A$24=($D$2)), --('Sales&Income'!A10:A24<=($G$2))) You don't need to (AND SHOULDN'T!) use -- on the col F range. If they're numbers or blank cells, you can sum them directly. So try =SUMPRODUCT(--('Sales&Income'!$A$10:$A$24=$D$2), --('Sales&Income'!A10:A24<=$G$2),'Sales&Income'!$F$1 0:$F$24) I also want to sum the aforementioned Column G for the same date parameters, but =SUMPRODUCT(--('Sales&Income'!$G$10:$G$24), --('Sales&Income'!$A$10:$A$24=($D$2)), --('Sales&Income'!A10:A24<=($G$2))) returns #VALUE! I suspect it is doing this because it doesn't like the 'value if false' result ("") of the =IF(C10="Sales",F10/9,"") formula. . . . Correct diagnosis. Use the same sort of formula as I proposed above for column F. =SUMPRODUCT(--('Sales&Income'!$A$10:$A$24=$D$2), --('Sales&Income'!A10:A24<=$G$2),'Sales&Income'!$G$1 0:$G$24) When ranges or arrays appear as separate arguments WITHOUT --s or any other arithmetic operators, SUMPRODUCT ignores text values in the range/array. For example, =SUMPRODUCT({1;"";3;4;"x"}) returns 8, while =SUMPRODUCT(--{1;"";3;4;"x"}) returns #VALUE!. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct problem... | Excel Worksheet Functions | |||
sumproduct problem | New Users to Excel | |||
Problem using sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) | |||
SUMPRODUCT problem | Excel Worksheet Functions |