1

Given a range of cells, each holding a single uppercase letter or its lowercase version, can I find out how many lowercase vs. uppercase characters there are, without using named ranges?

Like, A1 = "S", A2 = "s".

This CountIf both of them, so that doesn't work?

=COUNTIF(A1:A2,"S")

While mixing SumProduct with Exact, seems to require Named Ranges, (calling A1:A2, "Letters".) which I don't want, since I want be able to know what I'm doing just by looking at my formulas.

I tried replacing Letters with {A1,A2}, but that doesn't seem to work?

=SUMPRODUCT(--EXACT("D",Letters))

  • I can't see why would you need to use named ranges?? Maybe you could post some screenshots and the exact formula you've tried. – Máté Juhász Jul 11 at 23:48
  • The solution is likely to be different depending on whether the value is a single character, as in your example. Does your problem actually have just a single character per cell, or is this a simplified example? Also, will every cell be guaranteed to contain one and only one character, which is a letter, or could there be empty cells, cells with numbers, etc.? – fixer1234 Jul 11 at 23:58
  • @fixer1234 - Single characters in cells... And every cell in the range has a letter... Gonna specify that... – Malady Jul 12 at 0:05
  • 1
    @MátéJuhász - I just tried with an actual range A1:A2, instead of a list of cells, and it worked... I didn't know those were functionally different things... I might answer my own question, if no one else answers within a few days. – Malady Jul 12 at 0:08
  • You have mixed up searchrange with criteria. – JvdV Jul 12 at 6:06
3

About your own formula; =SUMPRODUCT(--EXACT("D",Letters)), you have mixed up the lookuprange with the criteria parameter, swap them around to =SUMPRODUCT(--EXACT(Letters, "D"))

enter image description here

If your goal was indeed to get the total of upper case and lower case I think what you were looking for was both the UPPER and LOWER functions?

enter image description here

Formula in B1:

=SUMPRODUCT(--(EXACT(A1:A7,UPPER(A1:A7))))

Formula in B2:

=SUMPRODUCT(--(EXACT(A1:A7,LOWER(A1:A7))))
  • 1
    Way better than mine. :-) – fixer1234 Jul 12 at 6:57
1

In your example, you show testing for a specific uppercase or lowercase letter. It wasn't clear whether that's your actual requirement or that's what you came up with as a first pass at a solution. If you are testing for a specific character, it looks like you already have a solution. If you want a more general solution, where you want to count all uppercase or all lowercase letters, you could use something like this:

enter image description here

Uppercase formula:

=SUMPRODUCT(--(CODE(A1:A10)>64)*(CODE(A1:A10)<91))

Lowercase formula:

=SUMPRODUCT(--(CODE(A1:A10)>96)*(CODE(A1:A10)<123))

This counts characters that fall into the uppercase or lowercase range of the ASCII table. A named range isn't required.

0

There are two formula in Excel that handles ASCII Character Set.

One is =CHAR(Number) which returns text eg. =CHAR(65) returns A.

Other is =CODE(Text) which returns the ASCII Code eg. =CODE("A") returns 65.

So enter =CODE(A2) in Cell B2. You will get number 65 if letter is "A"(Capital) and Number 97 if letter is "a" (Small).

So Capital Letters have ASCII Code from Number 65 to 90 and small letters have ASCII Code from Number 97 to 122.

Once you get numeric Codes, you get your results by applying the formula

=(if B2 >=92, "Small","Capital") etc. Once you get Codes you can apply various Conditions to get the desired results.

Using all such Codes one can send Secret Documents by Changing the Codes by some amount say 13. Send the Documents and On Call give the COde is 13.

One can have complete list by writing Numbers 0 to 255 in Column A and entering formula =CHAR(A2); COpy down the Column.

ASCII is 8 Bits Code. So it is upto 255.

Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.