E
Epinn
Biff, I think I should start my own thread ......
It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT.
=COUNTIF(C:C,"A*")
or
=COUNTIF(C1:C1000,"A*")
We can't use column nor wildcard with SUMPRODUCT for version 2003. (As per Biff, okay to use column for 2007.)
So, we have to use LEFT( ).
=SUMPRODUCT(--(LEFT(C1:C1000)="A"))
Now, if I want to use COUNTIF and my criterion is "A*" when "*" is not wildcard this time, how do I tell Excel? The tilde sign "~" seems to work. Can someone confirm, please?
=COUNTIF(C:C,"A~*")
or
=COUNTIF(C1:C1000,"A~*")
or
=SUMPRODUCT(--(LEFT(C1:C1000,2)="A*"))
If I want "A**" is there an easier way than A~*~*
Thanks!
Epinn
It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT.
=COUNTIF(C:C,"A*")
or
=COUNTIF(C1:C1000,"A*")
We can't use column nor wildcard with SUMPRODUCT for version 2003. (As per Biff, okay to use column for 2007.)
So, we have to use LEFT( ).
=SUMPRODUCT(--(LEFT(C1:C1000)="A"))
Now, if I want to use COUNTIF and my criterion is "A*" when "*" is not wildcard this time, how do I tell Excel? The tilde sign "~" seems to work. Can someone confirm, please?
=COUNTIF(C:C,"A~*")
or
=COUNTIF(C1:C1000,"A~*")
or
=SUMPRODUCT(--(LEFT(C1:C1000,2)="A*"))
If I want "A**" is there an easier way than A~*~*
Thanks!
Epinn