SUMIF with AND criteria

  • Thread starter Thread starter Amy
  • Start date Start date
A

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.
 
-----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.
.
Click to expand...

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))}
 
Alan, Just re-tested with
[reformatted]
________B_______C
1__quantity__price 2
2______3______ 12
3______4______ 57
4______2______ 12
5______4______100
6______3______ 12
7______3______ 12
8______3______ 12

=SUMPRODUCT((B1:B7=3)*(C2:C8=12)) to get 2 when ans is 3
Click to expand...

You're mixing & matching. The OP had ranges of different sizes, but your formula

=SUMPRODUCT((G2:G6=3)*(H3:H7=12))

has two ranges of the same size, just offset by one row as well as by one
column. Had you written =SUMPRODUCT((G2:G6=3)*(H3:H6=12)) your would have made
your point.

As for the example above, (B1:B7=3) gives

{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE}

and (C2:C8=12) gives

{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE}

so (B1:B7=3)*(C2:C8=12) gives

{0;0;0;0;0;1;1}

and the sum of this most definitely is 2. When you say the answer 'should be' 3,
there's an implicit 'if I hadn't intentionally written the formula incorrectly'
that should be stated explicitly. Given your example, your formula represents a
design error: it *should* be using the *same* rows in columns B and C. In the
OP's problem, who knows? If row 3 in column AK corresponded to row 4 in column
V, the OP could have used

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

which gives a result (unclear if it'd be the intended result, but it wouldn't be
#VALUE!).
 
Answer is 2.
(B1=3*C2=12)=0
(B2=3*C3=12)=0
(B3=3*C4=12)=0
(B4=3*C5=12)=0
(B5=3*C6=12)=0
(B6=3*C7=12)=1
(B7=3*C8=12)=1

Alan Beban
 
Harlan & Alan,

I may have mis-understood OP's intent.
I assumed OP wanted the count on the same row, ie row 2 has 3 &12, as does
row 6 & 7. All of these rows are within the formula of b1 or b2 to c7 or c8.
So, if intent is as I assumed, 3 should be the answer. Oh well, OP hasn't
responded (Amy where are you?), so I guess we will never know what she
wanted.
======
 
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
 
Back
Top