SUMPRODUCT #VALUE! problem

[Guest]

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!

 

[Bob Phillips]

=SUM(IF(('Sales&Income'!$G$10:$G$24<>"")*('Sales&Income'!$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)

 

[Peo Sjoblom]

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)

 

[Harlan Grove]

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$10:$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$10:$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!.

 

[Guest]

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.