Text Data Series

[Hurleymc]

I am by no means an Excel expert so I may be missing something easy
here but I'm trying to write a sumproduct statement that will check
multiple conditions before counting an entry in my master spreadsheet.

I have a column that contains identifiers that I'm defining, I have a
column that contains origin states, and I have a column that defines
destination states. The problem I'm having is I need to group the
states into zones and I'm trying to define the zones as a data series
and labeling them.

This is my formula:
=SUMPRODUCT(--([JanMarRaw.xls]Sheet1!$H$2:$H$2330="XXXX"),(--([JanMarRaw.xls]Sheet1!$D$2:$D$2330=west)))

where H2:H2330 contains my identifiers and D22330 contains the
origin states.
In another sheet, I have two cells (one containing "WA" and the other
containing "CA") and those two are defined as west (for the sake of
this post, WA and CA are the only states in my west region). So, I'm
trying to get a count of everything with XXX identifier and EITHER CA
or WA as it's origin state code. When I enter in this formula, I get
a #value! error but if I change the formula to $D$2$2330="CA" (or
"WA"), I will get an accurate count. I know it's freaking when it's
trying to match CA to the series of text entries (CA and WA) but I
don't know how to tell it to count if if it's included in that text
series.

 

[Frank Kabel]

Hi
some ways:
1. hardocded values:
=SUMPRODUCT(--([JanMarRaw.xls]Sheet1!$H$2:$H$2330="XXXX"),--(([JanMarRa
w.xls]Sheet1!$D$2:$D$2330="WA")+([JanMarRaw.xls]Sheet1!$D$2:$D$2330="CA
")>0))

2. Assumption: your cells A1:A2 contain these values:
=SUMPRODUCT(--([JanMarRaw.xls]Sheet1!$H$2:$H$2330="XXXX"),--(ISNUMBER(M
ATCH([JanMarRaw.xls]Sheet1!$D$2:$D$2330,$A$1:$A$2,0))))