# IF/COUNT Function Help

1. ## 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.  Register To Reply

2. ## Re: IF/COUNT Function Help

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)  Register To Reply

3. ## Re: IF/COUNT Function Help

Would you clarify something.

With one formula are you looking to count whether an Ucase/Lcase character appears in a single cell or across several cells? Menton of the COUNT function rather suggests looking across several cells.

It would help if you could upload a workbook with a few simple examples. Manually add the results you expect to see.  Register To Reply

4. ## Re: IF/COUNT Function Help

I want it to check multiple cells and to return 2 when true and 0 when false, so I entered in
=IF(EXACT(LOWER(H3:H12),H3:H12),2,0)
But that left me with a #VALUE! error. Did I input it wrong?  Register To Reply

5. ## Re: IF/COUNT Function Help

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)  Register To Reply

6. ## Re: IF/COUNT Function Help

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.  Register To Reply

7. ## Re: IF/COUNT Function 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.  Register To Reply

8. ## Re: IF/COUNT Function Help

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.  Register To Reply

9. ## Re: IF/COUNT Function Help

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.  Register To Reply

10. ## Re: IF/COUNT Function Help

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)  Register To Reply

11. ## Re: IF/COUNT Function Help 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?  Register To Reply

12. ## Re: IF/COUNT Function Help

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.  Register To Reply

13. ## Re: IF/COUNT Function Help

Here's one way to do what you are asking in post #12:

=LOOKUP(COUNTA(D3:D12),{0,6,7,8,9,10},{0,1,2,3,4,5})  Register To Reply

14. ## Re: IF/COUNT Function Help

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

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

15. ## Re: IF/COUNT Function Help

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

16. ## Re: IF/COUNT Function Help

Happy to help. Thanks for the rep!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 