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

 

[VoG]

Try

=SUMPRODUCT(--('2011'!S2:S400="RGB"),--('2011'!C2:C400="LIVE - SOLD"),'2011'!U2:U400)

 

[barry houdini]

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

Hello rgbbrown, welcome to MrExcel

The formula you tried should also work.......but unlike with SUMPRODUCT you need to confirm that formula with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

 

[AMAS]

Peter caught me from putting my foot in my mouth. I was going to suggest COUNTIFS but then realized that it wasn't available in XL 03. SumProduct is right approach. Good call Peter.

AMAS

 

[AMAS]

Good use of an array formula Barry .

AMAS

 

[rgbbrown]

Rarrh! You're all amazing - and quick!!

I THINK it's worked!!! I had to do that CTRL + Shift + Enter thing. I tried the Sum Product and got "#VALUE!" whatever that is!

Hmm.... I've just thought... I need to now add another criteria LOL. I only want it to calculate sum if '2011'!D2:D400 says "CL". I have either CL (Cross Lead) or CS (Cross Sale) so I'm trying to do a sum for each, separately on the last page see. How would I nest a FURTHER criteria?

 

[VoG]

There was a typo in my original SUMPRODUCT formula. Try


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

 

[AMAS]

I feel like I am in Jeopardy (can't hit the buzzer in time to submit my response ).

Another way is array formula using mulitple IF and SUM like you first proposed:

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

entered as Ctrl + Shift + Enter (CSE) not just Enter

Personally, I like using SumProduct instead but its all good.

AMAS

 

[rgbbrown]

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

Have to say, I'm more of an Apple freak but Microsoft got it spot on with Excel. Best thing they ever made (and probably will ever make!)

Thanks again guys.

 

[rgbbrown]

Hi AMAS - thanks for that too... although your idea just proposed a "0" value :S