COUNTIF(LEFT('SpreadSheet1!A1:A25, 1), "A")

[Dorian C. Chalom]

How would I create this function to do a Count If the left character in a
range of cells is an "A"?

COUNTIF(LEFT('SpreadSheet1!A1:A25, 1), "A")



Thank you.

 

[KL]

COUNTIF('SpreadSheet1!A1:A25,"A*")

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[Guest]

=SUMPRODUCT(--(LEFT('SpreadSheet1!A1:A25, 1)="A"))

 

[Dorian C. Chalom]

Thanks that did the trick...

COUNTIF('SpreadSheet1!A1:A25,"A*")

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile:
https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

How would I create this function to do a Count If the left character in a
range of cells is an "A"?

COUNTIF(LEFT('SpreadSheet1!A1:A25, 1), "A")



Thank you.

 

[Guest]

I like COUNTIF but don't include the ' in front of the sheet name.

You can eliminate the sheet reference alltogether if you name the range, for
example suppose I name it D for data. Then here are some of your possible
formulas:

=COUNTIF(D,"a*")
=SUMPRODUCT(N(LEFT(D)="a"))
=SUM(N(LEFT(D)="a")) entered as an array

Note since you are looking for the first character you don't need to specify
the second argument of the LEFT function. Also, although it is in vogue to
use -- to convert TRUE,FALSE to 1,0 N is shorter in this case.

Note also that none of these formulas are case sensitive, but you can do a
case sensitive version.
--
Cheers,
Shane Devenshire

COUNTIF('SpreadSheet1!A1:A25,"A*")

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

How would I create this function to do a Count If the left character in a
range of cells is an "A"?

COUNTIF(LEFT('SpreadSheet1!A1:A25, 1), "A")



Thank you.

 

[KL]

I like COUNTIF but don't include the ' in front of the sheet name.

I guess that's an obvious missprint deserving no comments

=SUMPRODUCT(N(LEFT(D)="a"))

The subtle difference is that the SUMPRODUCT formula is about 12 times slower than the COUNTIF one in 5000 cells ;-)

=SUM(N(LEFT(D)="a")) entered as an array

This one is even slower than SUMPRODUCT, although the gap is far less significant

Also, although it is in vogue to
use -- to convert TRUE,FALSE to 1,0 N is shorter in this case.

It is shorter to write, but seems to be slightly slower to calculate (according to C.Williams' plug-in)

Regards,
KL

 

[Guest]

Hi KL,

It wasn't a typo on your part, it was in the original question.

The most important point in my comment is that of using range names.

And like I said COUNTIF is the approach of choice.

On average array entered formulas are about 1.1 - 1.2 times slower than
SUMPRODUCT although the files size may be greater for the latter. Most
people are more interested in speed, not file size.