SUMIF with AND criteria

[Amy]

Please help correct this syntax...

=SUM(IF((AK3:AK5="4-Innovate to Protect")+(V3:V6="Increase
Awareness"),1,0))

I am getting the #VALUE error.

 

[Don Guillett]

1. ranges don't match
2. what, exactly, are you trying to do?

 

[Harald Staff]

=COUNTIF(AK3:AK5,"4-Innovate to Protect")+COUNTIF(V3:V6,"Increase
Awareness")

 

[david]

-----Original Message-----
Please help correct this syntax...

=SUM(IF((AK3:AK5="4-Innovate to Protect")+ (V3:V6="Increase
Awareness"),1,0))

I am getting the #VALUE error.
.

The best way to sum when the occurrence of two criteria is
met is with the Table array.


Date Description Account Job Amount
12/1/2000 Hotel 8010 702 700.00
12/2/2000 Hotel 8010 701 600.00
12/3/2000 Hotel 8010 701 500.00
12/4/2000 Hotel 8010 701 400.00
12/5/2000 Hotel 8010 701 300.00
12/6/2000 Hotel 8010 701 200.00
12/2/2000 Hotel 8020 702 100.00
12/3/2000 Airfare 8020 701 500.00
12/4/2000 Meal 8030 701 1,000.00
12/4/2000 Meal 8030 702 2,000.00
6,300.00

Account Job
701 702 Total
8010 2,000.00 700.00 2,700.00
8020 500.00 100.00 600.00
8030 1,000.00 2,000.00 3,000.00
3,500.00 2,800.00 6,300.00

These formulas are in the cell where the $2,000 and $700
in the above table. The formula requires a 3 key
combination to get the { at the front and the } at the
end. Hold CTRL SHIFT and press ENTER when you finish the
formula. And it's tricky to copy as the instructions in
Excel help say. Try it.

{=SUM(($C$3:$C$12=$B17)*($D$3:$D$12=C$16)*($E$3:$E$12))}
{=SUM(($C$3:$C$12=$B17)*($D$3:$D$12=D$16)*($E$3:$E$12))}

 

[Don Guillett]

As it turns out, after OP coming to me privately again, matches on the SAME
ROW is what was desired.
So, =SUMPRODUCT(('Campaigns & Tactics'!V3:V20="Increase
Awareness")*('Campaigns & Tactics'!AK3:AK20="4-Innovate to Protect")) was
the proper formula. Sometimes we have a hard time figuring out what OP's
want.
Paul Newman had that problem in "Cool Hand Luke" when it was said "What we
have here is a falure (sp) to cummicate (sp)"

v ak
Industry Thought Leadership 2, 3, and 4 (Marketing Priority)
Increase Awareness 2, 3, and 4 (Marketing Priority)
Industry Thought Leadership 4-Innovate to Protect
Increase Awareness 4-Innovate to Protect
Increase Awareness 4-Innovate to Protect
Increase Awareness 4-Innovate to Protect
Increase Awareness


======== 1-Quality First, Then Fix




THANK YOU THANK YOU! It works now! Someday maybe I'll actually understand
why it works, but that as you say, is later.

Thanks again,
Amy

-----Original Message-----
From: Barnett, Amy
Sent: Thursday, September 11, 2003 4:06 PM
To: 'Don Guillett'
Subject: RE: Re: SUMIF with AND criteria

Ok, I tried the formula you advised and am getting a "FALSE". Yes, I want
to count it if they are in the same row, so I used your second
formula...Advice? (New spreadsheet with the formula attached.)

-----Original Message-----
From: Don Guillett [mailto:[email protected]]
Sent: Thursday, September 11, 2003 3:39 PM
To: Barnett, Amy
Subject: Re: Re: SUMIF with AND criteria
======
If you want to count the instances of both on the SAME ROW, ranges must be
the same and this will do it.
=SUMPRODUCT(('Campaigns & Tactics'!V3:V20="increase awareness")*('Campaigns
& Tactics'!AK3:AK20="4-innovate to protect"))
BTW If it were me I would give each defined names but that's later.
=====
----- Original Message -----
From: "Barnett, Amy" <[email protected]>
To: "'Don Guillett'" <[email protected]>
Sent: Thursday, September 11, 2003 3:08 PM
Subject: RE: Re: SUMIF with AND criteria