PDA

View Full Version : sumif help please



Glaswegian
05-19-2011, 06:27 AM
Hi

I need some formula help.

I have a row of figures that is broken into 2 sections

B2:H2 then K2:N2

Some figures in this row will be negative values and I want to sum only the negatives. I can work out how to do the first half using Sumif but I can't seem to include the K to N part.

I'd appreciate some help with this.

Bob Phillips
10-05-2011, 02:30 AM
You could use

=SUMPRODUCT(--(N(OFFSET(B2:N2,0,{0;1;2;3;4;5;6;9;10;11;12},1,1))<0),--(N(OFFSET(B2:N2,0,{0;1;2;3;4;5;6;9;10;11;12},1,1))))

Glaswegian
10-05-2011, 03:31 AM
Thanks xld - I'd forgotten about this one!

Bob Phillips
10-05-2011, 03:52 AM
I only spotted it this morning, wonder how I missed it originally. I found it whilst looking you up because there is a user called TheRealTinTin on XtremVBTalk, and I was thinking it might be you.