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

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

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

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

"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.

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

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

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

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

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

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

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