Adding (Excel 2000)

Topic

New Reply

I have a worksheet that I keep a list of purchase orders.
On another sheet, I have the total dollars spent each month.

I want to also keep track of the amount of dollars spent on raw materials.
I added a column on the first worksheet and placed an “x” in the column for items that are raw material.
On the totals sheet, I tried to do a “if” statement but I can’t get it to work.

This is what I have now.
=IF(‘PURCHASE ORDERS’!B:B=”X”,SUM(‘PURCHASE ORDERS’!D:D),SUM(‘PURCHASE ORDERS’!D:D))
I wasn’t sure about what to make the false part do.

Any ideas would be appreciated.

Thanks,
Mark

Reply | Quote

Replies

You’ve got the order of the functions wrong: you want to SUM values IF they satisfy a condition. You can use =SUM(IF(…)), but it is easier to use the SUMIF function for this:

=SUMIF(‘PURCHASE ORDERS’!B:B,”x”,’PURCHASE ORDERS’!D:D)

Reply | Quote

A clarification point (that bites people periodically):

You could NOT do this with a SUM(IF…) ARRAY. The Array will NOT handle a full column. You would have to do only a partial column.

Steve

Reply | Quote

Thanks Steve,

I knew that, but didn’t mention it because in this case, it is a “normal” formula. It’s a useful tip for others who want to use a similar formula as an array formula.

Reply | Quote

It worked.

Thanks!!!

Reply | Quote