I have a list of items that were originated on a certain date, with
replacements noted underneath the original listings. The originals
all have an ID number like R1A, L3B, and so on; the replacements all
have the original ID plus -1, -2, and so forth. If an item has been
replaced, there will be a date next to it. The data will look
something like:

-ID- -Date-
R1A 1/20/09
R1A-1 1/21/09
R1A-2
R1B
R1C
R2A
R2B 1/20/09
R2B-1
R2C
etc

I need to keep a count of how many original items have been replaced.
I have a helper column off to the side to identify which IDs are for
the originals for filtering: =IF(LEN(A3)=3,"Y",""). Using the
Autofilter, I can filter on the helper column for all Y values, then
filter the dates for non-blanks to get a count of how many originals
have been replaced.

Is there any formula that will give me the same result? I'd like to
put that in a cell at the top of each of my sheets so I can see the
count at a glance.

Thanks.
Ed

=SUMPRODUCT(--(LEN(A2:A20)=3))

--
__________________________________
HTH

Bob

"Ed from AZ" <(E-Mail Removed)> wrote in message
news:8b6a8cd2-27e8-4d2f-9c6a-(E-Mail Removed)...
>I have a list of items that were originated on a certain date, with
> replacements noted underneath the original listings. The originals
> all have an ID number like R1A, L3B, and so on; the replacements all
> have the original ID plus -1, -2, and so forth. If an item has been
> replaced, there will be a date next to it. The data will look
> something like:
>
> -ID- -Date-
> R1A 1/20/09
> R1A-1 1/21/09
> R1A-2
> R1B
> R1C
> R2A
> R2B 1/20/09
> R2B-1
> R2C
> etc
>
> I need to keep a count of how many original items have been replaced.
> I have a helper column off to the side to identify which IDs are for
> the originals for filtering: =IF(LEN(A3)=3,"Y",""). Using the
> Autofilter, I can filter on the helper column for all Y values, then
> filter the dates for non-blanks to get a count of how many originals
> have been replaced.
>
> Is there any formula that will give me the same result? I'd like to
> put that in a cell at the top of each of my sheets so I can see the
> count at a glance.
>
> Thanks.
> Ed

That doesn't seem to be working, Bob. It returns the number of
original items, not the number of original items that have failed.
It's like it's not factoring in the second filter (Col B - dates not
blank). Also, what does the "--" do? I don't know that I've seen
that before.

Ed

On Jan 21, 10:09*am, "Bob Phillips" <Bob...@somewhere.com> wrote:
> =SUMPRODUCT(--(LEN(A2:A20)=3))
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Ed from AZ" <prof_ofw...@yahoo.com> wrote in messagenews:8b6a8cd2-27e8-4d2f-9c6a-(E-Mail Removed)...
>
>
>
> >I have a list of items that were originated on a certain date, with
> > replacements noted underneath the original listings. *The originals
> > all have an ID number like R1A, L3B, and so on; the replacements all
> > have the original ID plus -1, -2, and so forth. *If an item has been
> > replaced, there will be a date next to it. *The data will look
> > something like:
>
> > -ID- * * * * * *-Date-
> > R1A * * * * *1/20/09
> > R1A-1 * * * 1/21/09
> > R1A-2
> > R1B
> > R1C
> > R2A
> > R2B * * * * *1/20/09
> > R2B-1
> > R2C
> > etc
>
> > I need to keep a count of how many original items have been replaced.
> > I have a helper column off to the side to identify which IDs are for
> > the originals for filtering: =IF(LEN(A3)=3,"Y",""). *Using the
> > Autofilter, I can filter on the helper column for all Y values, then
> > filter the dates for non-blanks to get a count of how many originals
> > have been replaced.
>
> > Is there any formula that will give me the same result? *I'd like to
> > put that in a cell at the top of each of my sheets so I can see the
> > count at a glance.
>
> > Thanks.
> > Ed- Hide quoted text -
>
> - Show quoted text -

=sumproduct(--(len(a2:a20)=3),--(isnumber(b2:b20)))
(Bob didn't notice the second column stuff.)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Ed from AZ wrote:
>
> That doesn't seem to be working, Bob. It returns the number of
> original items, not the number of original items that have failed.
> It's like it's not factoring in the second filter (Col B - dates not
> blank). Also, what does the "--" do? I don't know that I've seen
> that before.
>
> Ed
>
> On Jan 21, 10:09 am, "Bob Phillips" <Bob...@somewhere.com> wrote:
> > =SUMPRODUCT(--(LEN(A2:A20)=3))
> >
> > --
> > __________________________________
> > HTH
> >
> > Bob
> >
> > "Ed from AZ" <prof_ofw...@yahoo.com> wrote in messagenews:8b6a8cd2-27e8-4d2f-9c6a-(E-Mail Removed)...
> >
> >
> >
> > >I have a list of items that were originated on a certain date, with
> > > replacements noted underneath the original listings. The originals
> > > all have an ID number like R1A, L3B, and so on; the replacements all
> > > have the original ID plus -1, -2, and so forth. If an item has been
> > > replaced, there will be a date next to it. The data will look
> > > something like:
> >
> > > -ID- -Date-
> > > R1A 1/20/09
> > > R1A-1 1/21/09
> > > R1A-2
> > > R1B
> > > R1C
> > > R2A
> > > R2B 1/20/09
> > > R2B-1
> > > R2C
> > > etc
> >
> > > I need to keep a count of how many original items have been replaced.
> > > I have a helper column off to the side to identify which IDs are for
> > > the originals for filtering: =IF(LEN(A3)=3,"Y",""). Using the
> > > Autofilter, I can filter on the helper column for all Y values, then
> > > filter the dates for non-blanks to get a count of how many originals
> > > have been replaced.
> >
> > > Is there any formula that will give me the same result? I'd like to
> > > put that in a cell at the top of each of my sheets so I can see the
> > > count at a glance.
> >
> > > Thanks.
> > > Ed- Hide quoted text -
> >
> > - Show quoted text -

--

Dave Peterson

That did it, Dave! Thanks so much to both you and Bob.

Ed

On Jan 21, 10:55*am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> =sumproduct(--(len(a2:a20)=3),--(isnumber(b2:b20)))
> (Bob didn't notice the second column stuff.)
>
> Adjust the ranges to match--but you can't use whole columns (except in xl2007).
>
> =sumproduct() likes to work with numbers. *The -- stuff changes truesand falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:http://www..xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html
>
>
>
>
>
> Ed from AZ wrote:
>
> > That doesn't seem to be working, Bob. *It returns the number of
> > original items, not the number of original items that have failed.
> > It's like it's not factoring in the second filter (Col B - dates not
> > blank). *Also, what does the "--" do? *I don't know that I've seen
> > that before.
>
> > Ed
>
> > On Jan 21, 10:09 am, "Bob Phillips" <Bob...@somewhere.com> wrote:
> > > =SUMPRODUCT(--(LEN(A2:A20)=3))
>
> > > --
> > > __________________________________
> > > HTH
>
> > > Bob
>
> > > "Ed from AZ" <prof_ofw...@yahoo.com> wrote in messagenews:8b6a8cd2-27e8-4d2f-9c6a-(E-Mail Removed)...
>
> > > >I have a list of items that were originated on a certain date, with
> > > > replacements noted underneath the original listings. *The originals
> > > > all have an ID number like R1A, L3B, and so on; the replacements all
> > > > have the original ID plus -1, -2, and so forth. *If an item has been
> > > > replaced, there will be a date next to it. *The data will look
> > > > something like:
>
> > > > -ID- * * * * * *-Date-
> > > > R1A * * * * *1/20/09
> > > > R1A-1 * * * 1/21/09
> > > > R1A-2
> > > > R1B
> > > > R1C
> > > > R2A
> > > > R2B * * * * *1/20/09
> > > > R2B-1
> > > > R2C
> > > > etc
>
> > > > I need to keep a count of how many original items have been replaced.
> > > > I have a helper column off to the side to identify which IDs are for
> > > > the originals for filtering: =IF(LEN(A3)=3,"Y",""). *Using the
> > > > Autofilter, I can filter on the helper column for all Y values, then
> > > > filter the dates for non-blanks to get a count of how many originals
> > > > have been replaced.
>
> > > > Is there any formula that will give me the same result? *I'd liketo
> > > > put that in a cell at the top of each of my sheets so I can see the
> > > > count at a glance.
>
> > > > Thanks.
> > > > Ed- Hide quoted text -
>
> > > - Show quoted text -
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Is it possible to add a third array to the SUMPRODUCT where the text
notes down a different column do not contain a certain string? I
tried
=sumproduct(--(len(a2:a20)=3),--(isnumber(b2:b20)),--(search
("mystring",k2:k20)=0))
but it returned a VALUE error. I suspect because it would not
recognize the third range as a valid array?

I really appreciate this help! This is great to learn to do this!

Ed


On Jan 21, 10:55*am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> =sumproduct(--(len(a2:a20)=3),--(isnumber(b2:b20)))
> (Bob didn't notice the second column stuff.)
>
> Adjust the ranges to match--but you can't use whole columns (except in xl2007).
>
> =sumproduct() likes to work with numbers. *The -- stuff changes truesand falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:http://www..xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html
>
>
>
>
>
> Ed from AZ wrote:
>
> > That doesn't seem to be working, Bob. *It returns the number of
> > original items, not the number of original items that have failed.
> > It's like it's not factoring in the second filter (Col B - dates not
> > blank). *Also, what does the "--" do? *I don't know that I've seen
> > that before.
>
> > Ed
>
> > On Jan 21, 10:09 am, "Bob Phillips" <Bob...@somewhere.com> wrote:
> > > =SUMPRODUCT(--(LEN(A2:A20)=3))
>
> > > --
> > > __________________________________
> > > HTH
>
> > > Bob
>
> > > "Ed from AZ" <prof_ofw...@yahoo.com> wrote in messagenews:8b6a8cd2-27e8-4d2f-9c6a-(E-Mail Removed)...
>
> > > >I have a list of items that were originated on a certain date, with
> > > > replacements noted underneath the original listings. *The originals
> > > > all have an ID number like R1A, L3B, and so on; the replacements all
> > > > have the original ID plus -1, -2, and so forth. *If an item has been
> > > > replaced, there will be a date next to it. *The data will look
> > > > something like:
>
> > > > -ID- * * * * * *-Date-
> > > > R1A * * * * *1/20/09
> > > > R1A-1 * * * 1/21/09
> > > > R1A-2
> > > > R1B
> > > > R1C
> > > > R2A
> > > > R2B * * * * *1/20/09
> > > > R2B-1
> > > > R2C
> > > > etc
>
> > > > I need to keep a count of how many original items have been replaced.
> > > > I have a helper column off to the side to identify which IDs are for
> > > > the originals for filtering: =IF(LEN(A3)=3,"Y",""). *Using the
> > > > Autofilter, I can filter on the helper column for all Y values, then
> > > > filter the dates for non-blanks to get a count of how many originals
> > > > have been replaced.
>
> > > > Is there any formula that will give me the same result? *I'd liketo
> > > > put that in a cell at the top of each of my sheets so I can see the
> > > > count at a glance.
>
> > > > Thanks.
> > > > Ed- Hide quoted text -
>
> > > - Show quoted text -
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Does not contain?

=SUMPRODUCT(--(LEN(A2:A20)=3),
--(ISNUMBER(B2:B20)),
--(ISERROR(SEARCH("mystring",K2:K20))))

If you wanted to look for mystring, you would use:
--(isnumber(search(...)))





Ed from AZ wrote:
>
> Is it possible to add a third array to the SUMPRODUCT where the text
> notes down a different column do not contain a certain string? I
> tried
> =sumproduct(--(len(a2:a20)=3),--(isnumber(b2:b20)),--(search
> ("mystring",k2:k20)=0))
> but it returned a VALUE error. I suspect because it would not
> recognize the third range as a valid array?
>
> I really appreciate this help! This is great to learn to do this!
>
> Ed
>
> On Jan 21, 10:55 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > =sumproduct(--(len(a2:a20)=3),--(isnumber(b2:b20)))
> > (Bob didn't notice the second column stuff.)
> >
> > Adjust the ranges to match--but you can't use whole columns (except in xl2007).
> >
> > =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
> > to 1's and 0's.
> >
> > Bob Phillips explains =sumproduct() in much more detail here:http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> >
> >
> >
> >
> > Ed from AZ wrote:
> >
> > > That doesn't seem to be working, Bob. It returns the number of
> > > original items, not the number of original items that have failed.
> > > It's like it's not factoring in the second filter (Col B - dates not
> > > blank). Also, what does the "--" do? I don't know that I've seen
> > > that before.
> >
> > > Ed
> >
> > > On Jan 21, 10:09 am, "Bob Phillips" <Bob...@somewhere.com> wrote:
> > > > =SUMPRODUCT(--(LEN(A2:A20)=3))
> >
> > > > --
> > > > __________________________________
> > > > HTH
> >
> > > > Bob
> >
> > > > "Ed from AZ" <prof_ofw...@yahoo.com> wrote in messagenews:8b6a8cd2-27e8-4d2f-9c6a-(E-Mail Removed)...
> >
> > > > >I have a list of items that were originated on a certain date, with
> > > > > replacements noted underneath the original listings. The originals
> > > > > all have an ID number like R1A, L3B, and so on; the replacements all
> > > > > have the original ID plus -1, -2, and so forth. If an item has been
> > > > > replaced, there will be a date next to it. The data will look
> > > > > something like:
> >
> > > > > -ID- -Date-
> > > > > R1A 1/20/09
> > > > > R1A-1 1/21/09
> > > > > R1A-2
> > > > > R1B
> > > > > R1C
> > > > > R2A
> > > > > R2B 1/20/09
> > > > > R2B-1
> > > > > R2C
> > > > > etc
> >
> > > > > I need to keep a count of how many original items have been replaced.
> > > > > I have a helper column off to the side to identify which IDs are for
> > > > > the originals for filtering: =IF(LEN(A3)=3,"Y",""). Using the
> > > > > Autofilter, I can filter on the helper column for all Y values, then
> > > > > filter the dates for non-blanks to get a count of how many originals
> > > > > have been replaced.
> >
> > > > > Is there any formula that will give me the same result? I'd like to
> > > > > put that in a cell at the top of each of my sheets so I can see the
> > > > > count at a glance.
> >
> > > > > Thanks.
> > > > > Ed- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -

--

Dave Peterson

Hi,

I'm probably missing something here, but if you just want to count how many
originals have been replaced why not

=COUNT(B2:B100)

Where B2:B100 is the date column with the first possible date on row 2.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Ed from AZ" wrote:

> I have a list of items that were originated on a certain date, with
> replacements noted underneath the original listings. The originals
> all have an ID number like R1A, L3B, and so on; the replacements all
> have the original ID plus -1, -2, and so forth. If an item has been
> replaced, there will be a date next to it. The data will look
> something like:
>
> -ID- -Date-
> R1A 1/20/09
> R1A-1 1/21/09
> R1A-2
> R1B
> R1C
> R2A
> R2B 1/20/09
> R2B-1
> R2C
> etc
>
> I need to keep a count of how many original items have been replaced.
> I have a helper column off to the side to identify which IDs are for
> the originals for filtering: =IF(LEN(A3)=3,"Y",""). Using the
> Autofilter, I can filter on the helper column for all Y values, then
> filter the dates for non-blanks to get a count of how many originals
> have been replaced.
>
> Is there any formula that will give me the same result? I'd like to
> put that in a cell at the top of each of my sheets so I can see the
> count at a glance.
>
> Thanks.
> Ed
>

Hi, Shane. That won't work because Col B contains the dates of ALL
items replaced, not just the original items. Otherwise that would be
best. Sorry I wasn't clearer in my original example.

Thanks for chiming in.
Ed


On Jan 21, 1:27*pm, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Hi,
>
> I'm probably missing something here, but if you just want to count how many
> originals have been replaced why not
>
> =COUNT(B2:B100)
>
> Where B2:B100 is the date column with the first possible date on row 2.
>
> --
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
>
>
> "Ed from AZ" wrote:
> > I have a list of items that were originated on a certain date, with
> > replacements noted underneath the original listings. *The originals
> > all have an ID number like R1A, L3B, and so on; the replacements all
> > have the original ID plus -1, -2, and so forth. *If an item has been
> > replaced, there will be a date next to it. *The data will look
> > something like:
>
> > -ID- * * * * * *-Date-
> > R1A * * * * *1/20/09
> > R1A-1 * * * 1/21/09
> > R1A-2
> > R1B
> > R1C
> > R2A
> > R2B * * * * *1/20/09
> > R2B-1
> > R2C
> > etc
>
> > I need to keep a count of how many original items have been replaced.
> > I have a helper column off to the side to identify which IDs are for
> > the originals for filtering: =IF(LEN(A3)=3,"Y",""). *Using the
> > Autofilter, I can filter on the helper column for all Y values, then
> > filter the dates for non-blanks to get a count of how many originals
> > have been replaced.
>
> > Is there any formula that will give me the same result? *I'd like to
> > put that in a cell at the top of each of my sheets so I can see the
> > count at a glance.
>
> > Thanks.
> > Ed- Hide quoted text -
>
> - Show quoted text -

On Jan 21, 1:17*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Does not contain?
>
> =SUMPRODUCT(--(LEN(A2:A20)=3),
> * * * * * * --(ISNUMBER(B2:B20)),
> * * * * * * --(ISERROR(SEARCH("mystring",K2:K20))))
>
> If you wanted to look for mystring, you would use:
> * --(isnumber(search(...)))

Ah! I get it!! Yeah - I wasn't returning a number for the SUMPRODUCT
to use.

> > =sumproduct(--(len(a2:a20)=3),--(isnumber(b2:b20)),--(search
> > ("mystring",k2:k20)=0))

Thanks so much Dave!

Ed