Can I put this filter result into a formula?

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

Ed from AZ

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
 
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
 
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(...)))
 
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.
 
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
 
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(...)))
Click to expand...

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

Thanks so much Dave!

Ed
 
I have been trying different ways to incorporate LEFT into this, but
it's not working. I wanted to see if I could add where the left-most
character is "L".

I tried:
--(LEFT(C2:C20,1)="L")
and
--(ISNUMBER(LEFT(C2:C20,1)="L"))
but got errors and then ran out of ideas! Any thoughts?

Ed
 
That should work.

Maybe you share the complete formula you tried.


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

=SUMPRODUCT(--(LEN(A2:A20)=3),
--(ISNUMBER(B2:B20)),
--(trim(left(c2:c20,1)="l")),
--(ISERROR(SEARCH("mystring",K2:K20))))


Or maybe you have leading spaces in front???
 
The =trim() option should be below that "Or maybe you have leading spaces in
front???" line.
 
I dunno what magic you have, Dave! I could swear I did exactly that
[--(left(c2:c20,1)="l"),] and it didn't work, but yours workd great!
(Obviously, I _didn't_ do something ~exactly~ how you did - but I
deleted the mistakes and can't compare.)

Thanks a million!
Ed
 
Back
Top