OMG - Excel 2003 - SUMIF Function

[rgbbrown]

Hi folks.... I apologise if this has already been answered but I've tried copying other user's 'received answers' but to no avail and I've used up 3 and half hours of my life so far trying this out - I hope you can help.

Now I've used this fine, and I get the correct answer from it:
=SUMIF('2011'!S2:S400,"RGB",'2011'!U2:U400)

However I want to add a criteria so that the sum of RGB's sales only appear if those in '2011'!C2:C400 say "LIVE - SOLD". If they don't, I don't want them counted. I've tried all sorts, including below, to no avail:

=SUM(IF('2011'!S2:S400="RGB",IF('2011'!C2:C400="LIVE - SOLD",'2011'!$U$2:$U$400)))

I don't get it! Please help.... thank you millions

 

[rgbbrown]

Ignore me... my fault... I didn't CTRL + Shift + Enter LOL!!! It works fine !

 

[VoG]

VoG that's amazing! That works too, and with the CL/CS thing!!!!

You are welcome. If you want to understand how it works have a read of this http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[AMAS]

Thanks for the confirmation.

Good luck.

AMAS

 

[rgbbrown]

Cheers will do but if I'm honest, it just goes *whoosh* over my head haha. I always thought SUMPRODUCT displays the number of cells rather than a value but you managed to make it show the sum total in £ rather than the number of cells... although now I'm going to attempt counting the number of cells.

What I'm trying to do is keep a count of sales as well as value see. The pivot table is far too complicated for me.

 

[VoG]

To count rather than sum

=SUMPRODUCT(--('2011'!S2:S400="RGB"),--('2011'!C2:C400="LIVE - SOLD"),--('2011'!D2:D400="CL"))