SOLVED

excel countifs mystery

%3CLINGO-SUB%20id%3D%22lingo-sub-2189847%22%20slang%3D%22en-US%22%3Eexcel%20countifs%20mystery%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2189847%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20recently%20started%20using%20excel%20for%20my%20studies.%20I%20have%20been%20using%20countifs%20formulas%20but%20now%20i%20am%20stuck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20calculating%20the%20amount%20of%20people%20in%20a%20certain%20age%20range%20by%20using%20%3DCOUNTIFS(VolvoOceanAllData%5BYear%20of%20birth%5D%3B%22%26gt%3B1936%22%3BVolvoOceanAllData%5BYear%20of%20birth%5D%3B%22%26lt%3B1951%22).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEvery%20time%20i%20entered%20the%20formula%2C%20excel%20couldnt%20work%20with%20it%2C%20kept%20marking%20the%20quotation%20marks.%3CBR%20%2F%3EI%20asked%20my%20teachers%20advise%2C%20who%20entered%20the%20formula%20in%20her%20document%2C%20where%20it%20worked.%20She%20sent%20her%20document%20to%20me%2C%20and%20i%20copied%20the%20formula%20from%20her%20document%20(WHICH%20WAS%20EXACTLY%20THE%20SAME)%20to%20mine.%20I%20inserted%20the%20formula%20and%20it%20worked%2C%20but%20when%20I%20manually%20copy%20the%20formula%20and%20write%20it%20myself%2C%20it%20does%20not%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20no%20clue%20what%20the%20problem%20is%2C%20it%20almost%20seems%20like%20my%20hands%20typing%20the%20formula%20are%20not%20suited%20for%20excel%20%3A))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2189847%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2189917%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20countifs%20mystery%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2189917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F988988%22%20target%3D%22_blank%22%3E%40excelmaffia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EMake%20sure%20that%20you%20use%20straight%20quotes%20%22...%22%20and%20not%20curly%20(or%20smart)%20quotes%20%E2%80%9C...%E2%80%9D%3C%2FLI%3E%0A%3CLI%3EAre%20you%20sure%20that%20you%20use%20semi-colon%20%3B%20as%20list%20separator%3F%20This%20is%20usually%20combined%20with%20comma%20as%20decimal%20separator.%20If%20you%20use%20point%20as%20decimal%20separator%2C%20you%20should%20probably%20use%20comma%20as%20list%20separator.%3C%2FLI%3E%0A%3C%2FOL%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

I recently started using excel for my studies. I have been using countifs formulas but now i am stuck.

 

I was calculating the amount of people in a certain age range by using =COUNTIFS(VolvoOceanAllData[Year of birth];">1936";VolvoOceanAllData[Year of birth];"<1951").

 

Every time i entered the formula, excel couldnt work with it, kept marking the quotation marks.
I asked my teachers advise, who entered the formula in her document, where it worked. She sent her document to me, and i copied the formula from her document (WHICH WAS EXACTLY THE SAME) to mine. I inserted the formula and it worked, but when I manually copy the formula and write it myself, it does not work.

 

I have no clue what the problem is, it almost seems like my hands typing the formula are not suited for excel :))))

 

2 Replies
best response confirmed by excelmaffia (New Contributor)
Solution

@excelmaffia 

  1. Make sure that you use straight quotes "..." and not curly (or smart) quotes “...”
  2. Are you sure that you use semi-colon ; as list separator? This is usually combined with comma as decimal separator. If you use point as decimal separator, you should probably use comma as list separator.
Apparently the quotes were the problem, thank you!! On my keyboard there are no different types of quotes, but on the screen there are (when i use shift). Try to see the difference between these two... " ''. Anyway, problem solved, thanks again