• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif function between range A-Z

K

Kumar M B

New Member
Hi,

I have a requirement to count the number of revisions we made for a particular month. i have revision letters in one column "-" indicates new or no revision, rest are all Alphabets between A through Z. My requirement is to count number of revisions we had between A-Z in a range of cells. Is there a way we can do it?
I have attached a spreadsheet. I need to get the result in Cell C2. Please help...

Thanks in Advance!!!
 

Attachments

  • CountIF.xlsx
    11.8 KB · Views: 9
Mr Kumar,

There is many ways to do this,

1) =COUNTA(A2:A31)-COUNTIF(A2:A31,"-")
2) =SUM(COUNTIF(A2:A31,CHAR(ROW(65:90)))) with crtl+shf+enter

and many more




Kumar M B said:
Hi,

I have a requirement to count the number of revisions we made for a particular month. i have revision letters in one column "-" indicates new or no revision, rest are all Alphabets between A through Z. My requirement is to count number of revisions we had between A-Z in a range of cells. Is there a way we can do it?
I have attached a spreadsheet. I need to get the result in Cell C2. Please help...

Thanks in Advance!!!
Click to expand...
 
xlstime said:
Mr Kumar,

There is many ways to do this,

1) =COUNTA(A2:A31)-COUNTIF(A2:A31,"-")
2) =SUM(COUNTIF(A2:A31,CHAR(ROW(65:90)))) with crtl+shf+enter

and many more
Click to expand...

Thanks a lot xlstime. Both the formula worked great for me. But i wanted to understand why ROW(65:90) is used in the 2nd formula? will that work if i put different value there?
 
As per your requirement, you will have to count all the cells where there is no "-" in the cell. Hence I am taking the help of countif to count all the cells without "-" value.

There are total 30 cells in your data range, when I am using the formula:

=COUNTIF($A$2:$A$31,"<>-") it returns me 16 as output and when I use the formula =COUNTIF($A$2:$A$31,"-") it returns 14 as output.

I hope this is what is the requirement.

The Row(65:90) will return the serial numbers starting from 65 until 90 and using the CHAR function it returns the Alphabets starting from A to Z..
 
Thanks a lot Ramesh, that was my requirement. for some reason i didnt get the output as 16 instead it gave me 30. But now it is showing as 16 i am not sure what happened there.
 
Also thanks for the explanation on Row and CHAR functions, but still i am aware as to how 65:90 is taken not 1:20 or 1:25
 
Okay.. here is how it works..

In a blank excel worksheet.. select cells starting from A1 to A26 and in cell A1 type the formula as =CHAR(ROW(65:90)) using formula bar

After typing the formula, press Ctrl + Shift + Enter.. that would return the Alphabets starting from A to Z..those are the character codes for the capital Alphabets..using this we are counting all the cells with these Alphabets..

xlstime is providing that formula as another way of calculating the output..
 
Great Ramesh,

Kumar M B,

Hope this is clear now,

- I used ROW(65:90) to populate A-Z series, rest very clearly explained by Ramesh

Ramesh said:
Okay.. here is how it works..

In a blank excel worksheet.. select cells starting from A1 to A26 and in cell A1 type the formula as =CHAR(ROW(65:90)) using formula bar

After typing the formula, press Ctrl + Shift + Enter.. that would return the Alphabets starting from A to Z..those are the character codes for the capital Alphabets..using this we are counting all the cells with these Alphabets..

xlstime is providing that formula as another way of calculating the output..
Click to expand...
 
You must log in or register to reply here.
Back
Top