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

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="BLU E")*(STYLE=
"MODERN"))

Ed

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

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

Ed wrote:
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="BLU E")*(S
TYLE=
"MODERN"))

Ed

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

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)

"Ed" wrote in message
...
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="BLU E")*(STYLE=
"MODERN"))

Ed

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

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

"Frank Kabel" wrote in message
...
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

Ed wrote:
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="BLU E")*(S
TYLE=
"MODERN"))

Ed

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

Hi Ed
1. quite simple. Instead of ...*(DATE_COL=DATE(2003,1,1))*... write
....*(DATE_COL=DATE(2003,1,1))*(DATE_COL<=DATE(20 03,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 wrote:
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

"Frank Kabel" wrote in message
...
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

Ed wrote:
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="BLU E")*(S
TYLE=
"MODERN"))

Ed

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

"Ed" wrote in message
...
(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.

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?


You are right, SUMPRODUCT, unlike SUMIF, doesn't take whole columns.

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

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
InsertNamePaste 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")*(CHARGEABILI TY="HARDWARE/CFE"))


"Bob Phillips" wrote in message
...
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)

"Ed" wrote in message
...
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="BLU E")*(STYLE=
"MODERN"))

Ed

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

Okay - hit the button too soon! The XX value in the previous post is 139
values that match.

Ed

"Bob Phillips" wrote in message
...
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)

"Ed" wrote in message
...
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="BLU E")*(STYLE=
"MODERN"))

Ed

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

Hi Ed
good to hear.
Frank

Ed wrote:
Okay - hit the button too soon! The XX value in the previous post is
139 values that match.

Ed

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

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

Ed

"Frank Kabel" wrote in message
...
Hi Ed
good to hear.
Frank

Ed wrote:
Okay - hit the button too soon! The XX value in the previous post is
139 values that match.

Ed

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

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 wrote:
No! The *formula* didn't give me that - my manual AutoFilter count
did. The formula *Still* shows blank. Any ideas?

Ed

"Frank Kabel" wrote in message
...
Hi Ed
good to hear.
Frank

Ed wrote:
Okay - hit the button too soon! The XX value in the previous post
is 139 values that match.

Ed

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

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")*(CHARGEABILIT Y="HARDWARE/CFE"))

--

HTH

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

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

Ed

"Frank Kabel" wrote in message
...
Hi Ed
good to hear.
Frank

Ed wrote:
Okay - hit the button too soon! The XX value in the previous post is
139 values that match.

Ed

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

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

Bob Phillips wrote:
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")*(CHARGEABILIT Y="HARDWARE/CFE"))


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

Ed

"Frank Kabel" wrote in message
...
Hi Ed
good to hear.
Frank

Ed wrote:
Okay - hit the button too soon! The XX value in the previous post
is 139 values that match.

Ed

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

Thanks Frank.

--

HTH

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

"Frank Kabel" wrote in message
...
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

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

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

"Frank Kabel" wrote in message
...
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

Bob Phillips wrote:
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")*(CHARGEABILIT Y="HARDWARE/CFE"))


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

Ed

"Frank Kabel" wrote in message
...
Hi Ed
good to hear.
Frank

Ed wrote:
Okay - hit the button too soon! The XX value in the previous post
is 139 values that match.

Ed

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

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

"Ed" wrote in message
...
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