conditional sum: can I make the array flexible

**jash147** · 11-01-2007, 03:50 PM

Hi
Still struggling. I attach sample.xls which gives some idea of what i want to do.
Using: =SUM(IF($B$2:$B$13=12,IF($D$2:$D$13="General Waste",$O$2:$O$13,0),0))

I can add up the values in 'O' if they are from a particular place and if they are a certain type of waste.
I'd like to be able to sum O2:Q2 in the example, but this doesn't seem to work.

I'd also like to get a formula that can size the array flexibly based on the number of rows entered, so that i could produce one formula for all the sites. Entering $B$2:$B$13=B8 gives me a flexible formula as to the store, but I'm not sure how to go about the rest.

Any help appreciated.
James

**daddylonglegs** · 11-01-2007, 04:35 PM

You've got some circular references in there which make things difficult to do, e.g. Q7 contains a formula which refers to Q7

=(E7*M7)*$Q$7

If you get rid of those you can use a formula like

=SUM(IF($B$2:$B$13=B8,IF($D$2:$D$13="General Waste",$O$2:$Q$13)))

confirmed with CTRL+SHIFT+ENTER

...or to avoid CSE you can use SUMPRODUCT, i.e.

=SUMPRODUCT(($B$2:$B$13=B8)*($D$2:$D$13="General Waste")*($O$2:$Q$13))

**jash147** · 11-01-2007, 05:58 PM

thanks for that - the reference is irrelevant - it has come about because I cut and pasted the spreadsheet to get the headings in the right place. All it refers to is a figure for that month for days rental. It needs puting somewhere else & i forgot about it. I've changed it arbitrarily to AJ1.

That now works, so thanks: I wondered what the problem was as there was no error message.

**jash147** · 11-01-2007, 06:01 PM

How can I go about tackling this? I'm not sure where to start.

**jash147** · 11-02-2007, 02:25 AM

Thanks for help here.
My old brain takes some time to think things through, but with nudges I can now do what I was asked to do.
I must remember PLUG-INs for the future: I spent time looking for something I wasn't too clear about and couldn't find it it because it wasn't there.
Ho hum.

I hope this hasn't been too irritating.