SumIF-multiple conditions/OR

  • Thread starter Thread starter GaryW
  • Start date Start date
G

GaryW

I have a range of cells that I want to ck for a "Y", then in the next range
can be "STOP" or a value of "Go"? Kinda a mix of conditions.
 
Don Guillett said:
A bit more detail, perhaps.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


Example:
Click to expand...
A B C
N Run 100
N Pause 50
Y Stop 50
C exit 25
Y Go 10
Z Exec 20

So I need to only select those row w/"Y" AND column B can be one of two values
STOP or Go. I will sum the values in Col C. THX.
 
You could get by with something like this in say, D1:
=SUMPRODUCT((A1:A6="Y")*(ISNUMBER(MATCH(B1:B6,{"Go","Stop"},0)))*C1:C6)
 
Does not appear to find the string values, "Go,Stop"....they are in the file.
Does the ISNUMBER expect a numeric value or can it be a string? Many Thanks.
 
Does not appear to find the string values, "Go,Stop"....they are in the
file.
Does the ISNUMBER expect a numeric value or can it be a string?
Click to expand...

There could be extraneous white leading/trailing spaces in col B's data
throwing some apparent matches off. Try it with a TRIM to enhance matching,
viz.:
=SUMPRODUCT((A1:A6="Y")*(ISNUMBER(MATCH(TRIM(B1:B6),{"Go","Stop"},0)))*C1:C6)

ISNUMBER merely converts the matched array retuned into an array of
TRUEs/FALSEs, depending on whether the elements are numeric or non-numeric

---
 
Thanks to All, Don/Max & Robert. Your solutions all worked and your effort
to send a reply is greatly appreciated.
 
Back
Top