0

Is there any way to nest text operators within a countif?

I want to check if the first 4 characters of a file are in the form A**.

=Countif(Left(A1,4),"A??.") doesn't work, but I can put in a 

=Left(a1,4)

column and then do

=Countif(B1,"A??.") no problem.

Any way around this? Normally I would just use if, but it doesn't accept wildcards.

0

1 Answer 1

1

Oh, you mean left, not len. No those don't work together, you need a workaround like

=SUMPRODUCT(--(LEFT(A1:A10,4)="text"))

If you need to use wildcards, you'll need something like this -

=SUMPRODUCT(--ISNUMBER(SEARCH("a***",(LEFT(A1:A10,4)))))
2
  • I did mean left not len. I also meant to include the wildcard example in my question. Long day at work I guess. Thanks for your psychic answer though, I'll try it now
    – Some_Guy
    May 20, 2015 at 9:16
  • =ISNUMBER(SEARCH("A??.",(LEFT(C18,4)))) Works great for what I want it for thanks :) I wasn't using countif over a range, simply using it because it accepts wildcards and IF doesn't. ISNUMBER(SEARCH makes perfect sense though for a "does this contain". Thanks :)
    – Some_Guy
    May 20, 2015 at 9:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .