Can I put this filter result into a formula?

[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

 

[Bob Phillips]

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

 

[Ed from AZ]

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

 

[Dave Peterson]

=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]

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

Ed

 

[Ed from AZ]

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

 

[Dave Peterson]

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(...)))

 

[Shane Devenshire]

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.

 

[Ed from AZ]

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

 

[Ed from AZ]

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.

Thanks so much Dave!

Ed

 

[Ed from AZ]

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

 

[Dave Peterson]

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???

 

[Dave Peterson]

The =trim() option should be below that "Or maybe you have leading spaces in
front???" line.

 

[Ed from AZ]

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