IF/COUNT Function Help

I've just started taking an Excel course, and I've been getting along ok until now. I needed to figure out a formula to count the number of characters in cells, which I figured out, but I'm stuck on figuring out a formula that will look at cells, for example, D4 through D7, to see if there are uppercase letters, lowercase letters, numbers, or special characters. If there are uppercase, lowercase, etc, I want a cell to display a value, for example "1." My instructor recommended using the IF and COUNT functions, but I can't seem to figure out how they'd accomplish what I'm trying to do. Any help would be greatly appreciated.

Hello and welcome to the forum.

Here's one way to check if there are uppercase letters in a cell:

=EXACT(LOWER(A1),A1)

This will return TRUE when there are no uppercase letters in a cell anf FALSE when there are.

If you want it to return 1 when this is FALSE and 0 when TRUE, you can do that with the IF function like this:

=IF(EXACT(LOWER(A1),A1),0,1)

If you want to check a range of cells for any uppercase characters, you can use this:

=SUMPRODUCT(--(NOT(EXACT(LOWER(H3:H12),H3:H12))))>0

If you want to check a range of cells for any lowercase characters, you can use this:

=SUMPRODUCT(--(NOT(EXACT(UPPER(H3:H12),H3:H12))))>0

You can then change the result to 1 for TRUE and 0 for FALSE like this:

=IF(formula,1,0)

or like this:

=--(formula)

I have attached an except of some of the examples I'm trying to work out. Essentially, if there is a uppercase in a password (I need to check multiple cells, not just one), I need to assign the Uppercase Letters with a 2. If there isn't, I need to assign it with a 0.
This would be repeated with the lowercase numbers, numbers, and special characters respectively. For length, I need to count the amount of characters in the passwords (multiple cells again, not just one), which I did. I'd need to assign it a number based on the length, • 0 points if the password length is less than 6, 1 point if the password length is 6, 2 points if it is 7, 3 points if it is 8, 4 points if it is 9, 5 points if it is 10.
For that part, would an if-then statement work? I'm unsure how to format it. I'd just need one column, like D, shown, and I can figure out the rest. Thanks in advance for your help.

Falcondude, thank you for your help, that formula worked! Now I just need to figure out how to implement the numbers, special characters, and length values.

You're changing the criteria so my suggestions will be changed to answer post #6.

D14 =IF(SUMPRODUCT(--(NOT(EXACT(LOWER(D3:D12),D3:D12))))>0,2,0)
D15 =IF(SUMPRODUCT(--(NOT(EXACT(UPPER(D3:D12),D3:D12))))>0,2,0)
D16 =IF(COUNT(D3:D12)>0,2,0)
D18 =COUNTA(D3:D12)

I'll have to work on special characters.

My apologies, I misread my assignment;it was supposed to be 2, not 1, but I adjusted the formulas accordingly. I figured out D14 and D15 thanks to your post #5, and now D16 thanks to this recent post. For D18, I also had that COUNTA formula, but the part I don't understand is how I'd assign a specific number (1, 2, 3, etc) depending on what that COUNTA number produces. For example ,if that COUNTA formula results in 8, how do I assign a cell 3. If i'ts 9, how do I assign it as 4, etc. In any case, thank you tremendously for your help so far.

As a continuation of post #8, you can use this for the special characters:

D17 =IF(COUNTA(D3:D12)-SUM(COUNTIF(D3:D12,{"a","b","c","d","e","f","g","h","I","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z",0,1,2,3,4,5,6,7,8,9}))>0,2,0)

Originally Posted by dchen298

For example ,if that COUNTA formula results in 8, how do I assign a cell 3. If i'ts 9, how do I assign it as 4, etc.

I don't understand the logic here. Should the result always be the length minus 5 like in the two examples above?

The special character one worked, thank you so much!! I think I understand what you did and how you got that to work.
In regards to D18, I need to assign it "0 points if the password length is less than 6, 1 point if the password length is 6, 2 points if it is 7, 3 points if it is 8, 4 points if it is 9, 5 points if it is 10." So I found the length of each password with the COUNTA formula, just as you had, but I am confused at how I'd make it reflect those point values I quoted above. So for example, Since the length of the password in column D is 5, I'd need to assign D18 to 0.

Just for the fun of it, here's a shorter alternative to the formula from post #13:

=MAX(0,COUNTA(D3:D12)-5)

Thank you so much!! I understand how both of the formulas you posted work; thanks again!

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.