Excel issue - COUNTIF

marine1

marine1

Honorary Master
Joined
Sep 4, 2006
Messages
50,244
Reaction score
2,901
Location
A black hole in the universe - JHB
Hi guys having an issue
I have letters D and N in certain cells, need to count them at the end.
I tried the following:
=COUNTIF(B3:AF3,B4)

1735989627194.png


Keeps giving the following error
1735989644584.png

Any ideas what is happening?
Thanks
 
1735995848523.png

Use the 'fx' icon on the formula bar (or Formula ribbon) to open the function arguments window (see above)

After you have entered the range B3:AF3 in the range text box, you then press TAB, which moves you down to the criteria text box, where you type in the letter D (the alphabetic character you are trying to count) and then press TAB again - this puts the double quotation marks around the D to identify that it is NOT a value you are counting - then click OK or press ENTER.

Hey Presto! Excel will count the number of times it detects the letter D in your specified data range.

1735996386251.png

EDIT: This is what the formulas will look like if you 'Show Formulas' - the keyboard shortcut is CTRL+`(the key above TAB & below ESC)

1736003245494.png
 
Last edited:
Ivan Leon said:
View attachment 1785252

Use the 'fx' icon on the formula bar (or Formula ribbon) to open the function arguments window (see above)

After you have entered the range B3:AF3 in the range text box, you then press TAB, which moves you down to the criteria text box, where you type in the letter D (the alphabetic character you are trying to count) and then press TAB again - this puts the double quotation marks around the D to identify that it is NOT a value you are counting - then click OK or press ENTER.

Hey Presto! Excel will count the number of times it detects the letter D in your specified data range.

View attachment 1785255
Click to expand...
You are a damn genius thank you so much
I dont know why it wasnt working my way
 
marine1 said:
Hi guys having an issue
I have letters D and N in certain cells, need to count them at the end.
I tried the following:
=COUNTIF(B3:AF3,B4)

View attachment 1785232


Keeps giving the following error
View attachment 1785233

Any ideas what is happening?
Thanks
Click to expand...
It is possibly the SA settings in Regional options that causes this issue. Edit them so that the decimal is the . and not the ,
Or just the use ; instead of the comma when writing your formulas.

I would change it because when you get to certain adv formulas the ; doesn't work
 
TribbleZA said:
It is possibly the SA settings in Regional options that causes this issue. Edit them so that the decimal is the . and not the ,
Or just the use ; instead of the comma when writing your formulas.

I would change it because when you get to certain adv formulas the ; doesn't work
Click to expand...
I find this EXTREMELY frustrating. Wish it wasn't linked to regional settings.
 
Ivan Leon said:
I train MS Office desktop apps for a living - being doing it for nigh on 30 years - so if you ever need any formal training on Excel, Word, PowerPoint, Outlook or Project, give me a shout... ;)
Click to expand...
Are you also experienced with their other tools such a Power BI , Dynamics, Azure etc. ?
 
Ivan Leon said:
I train MS Office desktop apps for a living - being doing it for nigh on 30 years - so if you ever need any formal training on Excel, Word, PowerPoint, Outlook or Project, give me a shout... ;)
Click to expand...
Good to know, I normally contact @TribbleZA who also trains corporate company staff but sometimes she is not available coz her phone is on silent when in class.
 
RedViking said:
I find this EXTREMELY frustrating. Wish it wasn't linked to regional settings.
Click to expand...
First thing I do when training any MS Excel course is to get all the delegates to check their regional settings - make sure it is set to English (South Africa) & then change the short date from yy/MM/dd to dd MMM yyyy, then get them to change the decimal character for both numbers & currency from the comma to the decimal point, the digit grouping symbol from a comma to a space (if applicable), and also make sure that the list separator is set to the comma & NOT the semi-colon.

1736060467471.png

1736060651973.png

1736060718924.png
 
Ivan Leon said:
First thing I do when training any MS Excel course is to get all the delegates to check their regional settings - make sure it is set to English (South Africa) & then change the short date from yy/MM/dd to dd MMM yyyy, then get them to change the decimal character for both numbers & currency from the comma to the decimal point, the digit grouping symbol from a comma to a space (if applicable), and also make sure that the list separator is set to the comma & NOT the semi-colon.

View attachment 1785403

View attachment 1785405

View attachment 1785406
Click to expand...
This is awesome thank you, I have just done this all now.
Voetsek comma, I hate you.
 
Ivan Leon said:
First thing I do when training any MS Excel course is to get all the delegates to check their regional settings - make sure it is set to English (South Africa) & then change the short date from yy/MM/dd to dd MMM yyyy, then get them to change the decimal character for both numbers & currency from the comma to the decimal point, the digit grouping symbol from a comma to a space (if applicable), and also make sure that the list separator is set to the comma & NOT the semi-colon.

View attachment 1785403

View attachment 1785405

View attachment 1785406
Click to expand...
This is frustrating as it messes with the stuff I do for NZ.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter