Creating a Function in Excel 2003

[Patti]

I have a spreadsheet that first breaksdown expenses, between centers, ie
project or ministry, then secondly by expense type, ie cash or credit card,
then I need it to sum the column with actual $. I've tried to use the IF
function but I get error message...

=IF(D899="x", IF(F8:F99="x",SUM(H8:H99)))

Can anyone please help me...

Thanks so much

 

[Jacob Skaria]

Try
=SUMPRODUCT(--(D899="x"),--(F8:F99="x"),H8:H99)

If this post helps click Yes

 

[Pete_UK]

You can use this array* formula:

=SUM(IF((D899="x")*(F8:F99="y"),H8:H99))

*An array formula must be committed using the key combination of Ctrl-
Shift-Enter (CSE), rather than the usual <Enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
subsequently amend or edit the formula, you must use CSE again.

The references "x" and "y" can be cell references which hold the
values.

Alternatively, you could try this:

=SUMPRODUCT((D899="x")*(F8:F99="y"),H8:H99)

This does not need to be array-entered.

Remember to use absolute cell references for the ranges if you copy
the formula elsewhere (eg to pick up other values for "x" and "y").

Hope this helps.

Pete