Hi,
I have a spreadsheet which is to be used to monitor spend according to
specific cost codes. I am having problems though in writing formulas that
actually work..

Can anyone help in writing a formula to work out the total spend (£) (which
could be in any of the cells h44:h420), for spend which is on a certain cost
code e.g. '6210 - Oil' which can be in any of the cells e44:e420. The new
total (£) needs to go into cell d2.

If I can see how to do one cost code, I can then alter the formula to work
out the others.

note column d uses a drop down input

I'm sure its simple, but I'm struggling.

Cheers

Sorry, I missed out another condition of the formula.

Column J, (range j44:j420) must be marked Yes for the amout to be considered
in the formula

Please help!

"DaveAsh" wrote:

Hi,
I have a spreadsheet which is to be used to monitor spend according to
specific cost codes. I am having problems though in writing formulas that
actually work..

Can anyone help in writing a formula to work out the total spend (£) (which
could be in any of the cells h44:h420), for spend which is on a certain cost
code e.g. '6210 - Oil' which can be in any of the cells e44:e420. The new
total (£) needs to go into cell d2.

If I can see how to do one cost code, I can then alter the formula to work
out the others.

note column d uses a drop down input

I'm sure its simple, but I'm struggling.

Cheers

=SUMPRODUCT((H44:H420)*(E44:E420="6210 - Oil")*(J44:J420="Yes"))

--
Gary''s Student - gsnu200831


"DaveAsh" wrote:

Sorry, I missed out another condition of the formula.

Column J, (range j44:j420) must be marked Yes for the amout to be considered
in the formula

Please help!

"DaveAsh" wrote:

Hi,
I have a spreadsheet which is to be used to monitor spend according to
specific cost codes. I am having problems though in writing formulas that
actually work..

Can anyone help in writing a formula to work out the total spend (£) (which
could be in any of the cells h44:h420), for spend which is on a certain cost
code e.g. '6210 - Oil' which can be in any of the cells e44:e420. The new
total (£) needs to go into cell d2.

If I can see how to do one cost code, I can then alter the formula to work
out the others.

note column d uses a drop down input

I'm sure its simple, but I'm struggling.

Cheers

Thanks - That works like a dream

Regards

"Gary''s Student" wrote:

=SUMPRODUCT((H44:H420)*(E44:E420="6210 - Oil")*(J44:J420="Yes"))

--
Gary''s Student - gsnu200831


"DaveAsh" wrote:

Sorry, I missed out another condition of the formula.

Column J, (range j44:j420) must be marked Yes for the amout to be considered
in the formula

Please help!

"DaveAsh" wrote:

Hi,
I have a spreadsheet which is to be used to monitor spend according to
specific cost codes. I am having problems though in writing formulas that
actually work..

Can anyone help in writing a formula to work out the total spend (£) (which
could be in any of the cells h44:h420), for spend which is on a certain cost
code e.g. '6210 - Oil' which can be in any of the cells e44:e420. The new
total (£) needs to go into cell d2.

If I can see how to do one cost code, I can then alter the formula to work
out the others.

note column d uses a drop down input

I'm sure its simple, but I'm struggling.

Cheers

How about:

=SUMPRODUCT((H44:H420),--(E44:E420="6210 - Oil"))
--
Gary''s Student - gsnu200831


"DaveAsh" wrote:

Hi,
I have a spreadsheet which is to be used to monitor spend according to
specific cost codes. I am having problems though in writing formulas that
actually work..

Can anyone help in writing a formula to work out the total spend (£) (which
could be in any of the cells h44:h420), for spend which is on a certain cost
code e.g. '6210 - Oil' which can be in any of the cells e44:e420. The new
total (£) needs to go into cell d2.

If I can see how to do one cost code, I can then alter the formula to work
out the others.

note column d uses a drop down input

I'm sure its simple, but I'm struggling.

Cheers

If I understand your request I wouyld suggest the =DSUM formula. Hope that
helps

"DaveAsh" wrote:

Hi,
I have a spreadsheet which is to be used to monitor spend according to
specific cost codes. I am having problems though in writing formulas that
actually work..

Can anyone help in writing a formula to work out the total spend (£) (which
could be in any of the cells h44:h420), for spend which is on a certain cost
code e.g. '6210 - Oil' which can be in any of the cells e44:e420. The new
total (£) needs to go into cell d2.

If I can see how to do one cost code, I can then alter the formula to work
out the others.

note column d uses a drop down input

I'm sure its simple, but I'm struggling.

Cheers