Excel: Counting an alphabetic range

[mcwinter]

Hi there
I have got a spreadsheet with a database of people and all their details, Surname (E), Name (F), Gender (L), Date of Birth (M), Age (O), etc...

So I have a demographics sheet that I calculate ages, gender etc and would like to do the following 2 tables.

1. Working out ages between 2 specified ages that I input into set cells
E.g. I want all people between the ages of 13 and 18 years old (So i put 13 into cell A13 and 18 into B13. My formula will then sit in C13 and will Also count those ages.
I want to do this so to be able to change the ages if needed

2. Working out Surnames that sit between an alphabetic range. So as I did with ages I can have "A" into A51 and "D" in B51 and it will give me all the surnames that start with those letters in a range between A and D (Including the 2 options I have in those cells)

I hope I have been clear enough. Can anyone assist? (This can be formula or VB - I am slowly learning)
Thank you!

 

[Shrikant]

1. =countifs(o,">="&a13,o,"<="&b13)

 

[mcwinter]

Great thanks, didnt think of the & function for the ages. Now just to get the alphabetic range from surnames.
Much appreciated!

 

[Peter_SSs]

2. Similarly ..

Excel Workbook
	A	B	C	D	E
1
2					Smith
3					Jones
4					Brown
5					Kent
6					Young
7					Jones
8					Lyons
9
10
11
50
51	D	L	4
52
Count

 

[mcwinter]

Thank you Peter. So this will then only count the surname if it starts with the letter in that range?

 

[Peter_SSs]

Thank you Peter. So this will then only count the surname if it starts with the letter in that range?

Yes. Isn't that what you wanted?

My example counted Jones (twice), Kent and Lyons

 

[Shrikant]

To: Peter_SSs

Sir,
I was trying this formula.

=SUM(--(CODE(LEFT(I4:I14,1))=ROW(INDIRECT("A"&CODE(J3)&":A"&CODE(K3)))))

and then Ctrl+Shift+Enter

What I intent to do was get array of first letters of surname and then match it with the letter required.

I was successful in doing this for a single letter.

=SUM(--(LEFT(A3:A585,1)="A"))

this get me the desired result.

But when I made the second part an array and tried to compare, it failed.

can you explain please?

 

[mcwinter]

Yes. Isn't that what you wanted?

My example counted Jones (twice), Kent and Lyons

Yup Exactly what I wanted. Didn't realize that it's so simple haha. great thank you!

 

[Peter_SSs]

To: Peter_SSs

Sir,
I was trying this formula.

=SUM(--(CODE(LEFT(I4:I14,1))=ROW(INDIRECT("A"&CODE(J3)&":A"&CODE(K3)))))

and then Ctrl+Shift+Enter

What I intent to do was get array of first letters of surname and then match it with the letter required.

I was successful in doing this for a single letter.

=SUM(--(LEFT(A3:A585,1)="A"))

this get me the desired result.

But when I made the second part an array and tried to compare, it failed.

can you explain please?

I cannot explain what you are trying to do with ROW(INDIRECT etc as I have no idea.

In any case a solution with CODE() in it will error if there are any blank cells.
In any case you don't need to use code at all, you can just compare the letters. So you could have used an array formula like
{=SUM((LEFT(I4:I14,1)>=J3)*(LEFT(I4:I14,1)<=K3))}

But why not stick to a simple non-array COUNTIFS?

 

[Shrikant]

Definitely, yours is a better solution. If needed i would use that only.

But I am asking a fundamental question regarding array formulas.

Let me expound.
In array formula, we generally give multiple inputs in a function which expects a single input. and Then we press ctrl+shift+enter to make excel consider it as an array function.

In my successful attempt, I generated an array with LEFT function which gave me {"A";"A";"B"} like that and i compared with a single entry "A" to get results

In my unsuccessful attempt, I wanted to generate an array with LEFT function like above and then compare it with another array {"A";"B";"C"}
As it was difficult to make the second array in form of alphabets i converted them to numbers using CODE. so i could get 2 arrays with formulas successfully.

my question is can I compare/ perform an operation with 2 arrays in a single formula with Crtl+shift +Enter method?