Need help with array formula to count rows w/ mult.criteria

E

Ed

I need to count the number of rows containing certain items. I think an
array formula might be what I need. I've never done this before, and I'm
running into some difficulties.

I've got four columns as named ranges: ITEM, DATE, COLOR, and STYLE. ITEM
is a text filed as "L5-(some letter)(several numbers). DATE is formatted as
20030101. COLOR and STYLE are text. I want to chose an item as "L5-B*",
with dates between (for example) 20030101 and 20030201. Then I need to say
"for each COLOR, how many do I have of each STYLE"?

I Ctrl+Shift+entered the following formula (it showed up with brackets), but
got a "#N/A" error. Any help is greatly appreciated.

=SUM((ITEM="L5-B*")*(DATE>=20030101)*(DATE<=20030201)*(COLOR="BLUE")*(STYLE=
"MODERN"))

Ed
 
F

Frank Kabel

Hi Ed
try
SUMPRODUCT((NOT(ISERROR(FIND("L5-B",ITEM))))*(DATE_COL>=DATE(2003,1,1))
*(COLOR="BLUE")*(STYLE="MODERN"))
Some notes:
your ranges have to be defined like $A$1:$A$9999 ($A:$A won't work for
SUMPRODUCT). And all should consist of the same number of rows
Also I'm not sure if the name DATE is accepted by Excel as it is the
same as the Excel function -> I changed it in the above example to
DATE_COL

HTH
Frank
 
B

Bob Phillips

Hi Ed,

Try this

=SUMPRODUCT((NOT(ISERROR(FIND("L5-B",Item))))*(Date>=DATE(2004,1,1))*(Date<=
DATE(2004,2,1))*(Color="BLUE")*(Style="MODERN"))

It's not an array formula, so just normal enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
E

Ed

Thanks for the reply, Frank. I don't understand quite everything you've
given me, but I'll work through it. A few questions, if I may:
(1) I simplified things for the question, but I may have been too simple
with regards to the date. Rarely will it be that clean - it will be more
like >=2003104 AND <=20040323. I can't how to fit "between this date and
that date" into the formula you provided.
(2) If I read you right, I can't just select an entire column and name it;
I have to just select the used range and name that only?

Thanks for the help.
Ed
 
F

Frank Kabel

Hi Ed
1. quite simple. Instead of ...*(DATE_COL>=DATE(2003,1,1))*... write
....*(DATE_COL>=DATE(2003,1,1))*(DATE_COL<=DATE(2003,3,1))*...

2. You can easily create a name for a column like $A$1:$A$20000 just
the simple selections without row numbers is not allowed

Frank
 
B

Bob Phillips

Ed said:
(1) I simplified things for the question, but I may have been too simple
with regards to the date. Rarely will it be that clean - it will be more
like >=2003104 AND <=20040323. I can't how to fit "between this date and
that date" into the formula you provided.
Click to expand...

See my previous response on how to do that.

(2) If I read you right, I can't just select an entire column and name it;
I have to just select the used range and name that only?
Click to expand...
You are right, SUMPRODUCT, unlike SUMIF, doesn't take whole columns.
 
E

Ed

Bob and Frank: I don't know what's wrong, but it's showing blank when I
*know* there's data there! I constrained my ranges to just the used range
and "Date" was changed to "Dates". I copied the formula into a text editor
and replaced my simplified terms with the real ones, then pasted it into
Excel. When it just sat there like a blob of text, I used
Insert>>Name>>Paste to make sure all the range names were correct. I hit
Enter - blank. Selected and F9 - blank! But when I AutoFilter, I'm
rewarded with a count of XX for these criteria. Where did I screw up?

Ed

=SUMPRODUCT((NOT(ISERROR(FIND("L5-T*",TIR))))*(DATES>=DATE(2003,6,28))*(DATE
S<=DATE(2004,1,7))*(FDSC_CLASS="EFF")*(CHARGEABILITY="HARDWARE/CFE"))
 
Top