To the EXCEL GODS:

[Bestill]

I have a list of names in Column A. Some names end with an asterisk "*". Is there a way to count how many names in a specific range have an asterisk on the end and display the total number? I tried:

SUM(RIGHT(A1:A5,1)="*")

This didn't work. I also tried:

SUM(IF(RIGHT(A1:A5,1)="*",1,0))

The second one tells me that I get a value of 3 when I check it in the formula wizard, which is correct, but the cell in the spreadsheet gives me the error value "#VALUE!" Any suggestions?

.....Column A
1.......Joe
2.......John*
3.......Steve*
4.......Harry
5.......Paul*
6.......3

 

[Bestill]

bump

 

[Bestill]

Anyone know how to do this without using a macro?

 

[CodeJockey]

You can try not using a range:

=SUM(IF(RIGHT(A1,1)="*",1,0),IF(RIGHT(A2,1)="*",1,0),IF(RIGHT(A3,1)="*",1,0),IF(RIGHT(A4,1)="*",1,0),IF(RIGHT(A5,1)="*",1,0))

or, you can place the individual =IF() statements into column B (or anywhere else, like column Z, or even into a different worksheet), to create a list of 0's and 1's, then sum them (the 0's and 1's).

Don't ask me why the formula wizard lets you use a range, but the spreadsheet itself doesn't...different programmers interpretations of the spec., I guess.

 

[Rob G.]

SUMIF counts the actual value of the cells. To count the number of instances you use COUNTIF.

The problem here is that the asterisk is seen as a wildcard character. Somebody else may know a way of stopping that, but in the meantime consider this:

Change the * character for something else - say a !

Then use:

=COUNTIF(A1:A4,"=*!")

This counts the number of instances of a "!" preceeded by any characters.

 

[RayH]

You can use an array function.
Type the following formula then use Ctrl-Shift-Enter to enter it:

=SUM(IF(RIGHT(A1:A17,1)="*",1,0))

 

[CodeJockey]

Ray,
Array Formulas = cool. You could have saved me a lot of time over the past few years...why didn't you mention it sooner?

Bestil, looks like that is exactly what you are searching for.

 

[Bestill]

RayH: THANK YOU! That is exactly what I was looking for.

 

[RayH]

<< RayH: THANK YOU! That is exactly what I was looking for. >>



Glad to help out.