Hi, Jeroen-
Thanks for the quick reply. I'm using Excel 97. Yikes!
Sorry about all the range errors, I copied the formula
and then changed the references in this dinky little
Yahoo editor window. It should have been:
=SUMPRODUCT((Expenses!$A$2:$A$8="Office")*(Expenses!$B$2:$B8=
"Ink")*(Expenses!$C$2:$C$8))
Does that work in Excel 2003?
LBG
--- Jeroen Eeuwes <[EMAIL PROTECTED]> wrote:
> Hi LBG,
>
> > compatibility with Excel's SumProduct function,
> which
> > lets you examine a table and sum items in a given
> > column based on multiple criteria in one or more
> other
> > columns.
>
> In which version of Excel is this? In the version at
> our office (Excel
> 2003) this is not true. Sumproduct gives you the sum
> of the product of
> the matrixes you give as parameters. For example:
>
> A B C D
> 2 2 3 3
> 2 2 3 3
>
> =SUMPRODUCT(A1:B2;C1:D2) results in 24 (2*3 + 2*3 +
> 2*3 + 2*3).
>
> This is not different in OpenOffice.org.
>
> Perhaps you are confused with SUM.IF. This wil take
> a range, a
> criteria and a sum-range. If a cell in the first
> range fits the
> criteria the value in the sum-range wil be added tot
> the result of the
> formula. However, as far as I know this won't work
> if you have the
> criteria divided over two different cells. You can't
> give a valid
> criteria for that.
>
> >
>
=SUMPRODUCT((Expenses!$A$2:$A$8="Office")*(Expenses!$C$2:$C98=
> >"Ink")*(Expenses!$I$3:$I$498))
>
> In my version of Excel this gives an error (apart
> from the fact that
> the ranges are wrong).
>
> Perhaps you can check which formula is used. If you
> can't find the
> equivalent in OpenOffice.org you should post again
> with the formula.
>
> With regards,
> Jeroen Eeuwes
>
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
