Adding 2 Values When 3 Conditions are Met, Including an "OR"

[Guest]

I need to add up dollar amounts in Column D when Columns A and B meet certain
critieria and Column C meets either of two conditions. The following should
return 300

A B C D
Open Alabama Closed 100
Open Georgia Verbal 200
Open Alabama Verbal 100
Open Alabama
Open Alabama Verbal 100

I can do it with Column C meeting 1 condition, but can't figure out how to
include the other condition.

=SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF(C2:C5="Verbal",D26)))
works BUT

I need to add the condition "If C2:C6="Closed". i.e. I want to sum the
dollar amounts for all Open Accounts in Alablama that are either Closed or
Verbal. Sometimes Columns C and D are blank. Thanks, Anne M

 

[Excelenator]

Use this

=SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2:C6="Verbal") +
(C2:C6="Verbal"))*(D26))

I need to add up dollar amounts in Column D when Columns A and B meet
certain
critieria and Column C meets either of two conditions. The following
should
return 300

A B C D
Open Alabama Closed 100
Open Georgia Verbal 200
Open Alabama Verbal 100
Open Alabama
Open Alabama Verbal 100

I can do it with Column C meeting 1 condition, but can't figure out how
to
include the other condition.

=SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF(C2:C5="Verbal",D26)))
works BUT

I need to add the condition "If C2:C6="Closed". i.e. I want to sum
the
dollar amounts for all Open Accounts in Alablama that are either Closed
or
Verbal. Sometimes Columns C and D are blank. Thanks, Anne M

 

[Excelenator]

Use this

=SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2:C6="Verbal") +
(C2:C6="Closed"))*(D26))

I need to add up dollar amounts in Column D when Columns A and B meet
certain
critieria and Column C meets either of two conditions. The following
should
return 300

A B C D
Open Alabama Closed 100
Open Georgia Verbal 200
Open Alabama Verbal 100
Open Alabama
Open Alabama Verbal 100

I can do it with Column C meeting 1 condition, but can't figure out how
to
include the other condition.

=SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF(C2:C5="Verbal",D26)))
works BUT

I need to add the condition "If C2:C6="Closed". i.e. I want to sum
the
dollar amounts for all Open Accounts in Alablama that are either Closed
or
Verbal. Sometimes Columns C and D are blank. Thanks, Anne M

 

[Guest]

That returns 400 for me. Am I doing something wrong? Anne M

 

[Guest]

Ah, I wondered if that was it...was just trying the change. Thanks !

 

[Guest]

I'm sorry, but when I use this format for my 'real' data, it returns #VALUE!.
I modified my example for simplicity - thinking I could follow the format
and all would be well.....but appears not. Here is my true formula.

=SUMPRODUCT(('Raw Data'!A2:A477="Open")*('Raw
Data'!I2:I477="Cerner")*(('Raw Data'!J2:J477="Verbal")+('Raw
Data'!J2:J477="Closed"))*('Raw Data'!L2:L477))

Aren't I using the correct syntax? Sorry to be a problem, Anne M

 

[Excelenator]

I think the blanks are what is causing this problem. Can you select th
entire range of data press F5 and click "Special" and select th
"Blanks" radio button and click "OK"? Once that is done type in
single quote or zeror and hit CTL-ENTER. This will put a single quot
mark or zeror (or any other representation you want for blank cells) i
all the blank cells. Then Sumproduct should work

 

[Ragdyer]

Try this:

=SUMPRODUCT((A2:A477="Open")*(I2:I477="Cerner")*(J2:J477={"Verbal","Closed"})*L2:L477)

You can carefully add your paths.

 

[Excelenator]

Sorry the formula I posted was

=SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2:C6="Verbal") +
(C2:C6=*"Verbal"*))*(D26))


And it should have been

=SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2:C6="Verbal") +
(C2:C6=*"Closed"*))*(D26)