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

  • Thread starter Thread starter Ed
  • Start date Start date
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
 
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
 
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)
 
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
 
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
 
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.
 
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"))
 
Okay - hit the button too soon! The XX value in the previous post is 139
values that match.

Ed
 
No! The *formula* didn't give me that - my manual AutoFilter count did.
The formula *Still* shows blank. Any ideas?

Ed
 
Hi Ed
just mail me the sheet (frank[dot]kabel[at]freenet[dot]de) and I'll
look at it. Probably some text values within the range

Frank
 
Ed,

You still have the * in the first test. Both mine and Frank's formula used
ISERROR(FIND to circumvent this. Try this formula

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Ed, Bob
I just posted Ed a corrected version of his spreadsheet. Besides the
issue Bob mentioned regarding the FIND function, the date columns did
not contain dates but real numbers (e.g. 20030101). So both our date
comparisons didn't work -as it could be exspected in this case :-).
So after changing this and correcting the FIND function SUMPRODUCT
works

Regards
Frank
 
Thanks Frank.

--

HTH

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

So in this instance, FIND was looking for the "*" character, rather than
using it as a wildcard? I'll have to remember that one! And I should have
caught that date vs. number thing - a simple check of cell format would have
told me. *SIGH* All these details - guess that's what makes you guys good!
8>)

Thank you so much for all your help.
Ed
 
Ed,

Yes, you've got it. SUMIF and COUNTIF support wildcards, SUMPRODUCT doesn't,
but as you can see from Frank and my super-clever workaround <vbg>, there
are ways and means. The date thing was tricky, seeing it fornatted as a date
led us both to assume that it was a date (and we all know what assuming
does).

Anyway, it's good you are sorted.

Bob
 
Back
Top