SUMIFS or COUNTIFS Bug?

%3CLINGO-SUB%20id%3D%22lingo-sub-89295%22%20slang%3D%22en-US%22%3ESUMIFS%20or%20COUNTIFS%20Bug%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-89295%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20using%20a%20SUMIF%20and%20COUNTIF%20function%20to%20count%20the%20number%20of%20times%20months%20occur.%20My%20reference%20formula%20is%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF('PNL'!%24F2%3A%24F189%2C%22Sep%202018%22)%2BCOUNTIF('Misc'!%24F2%3A%24F189%2C%22Sep%202018%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20made%20a%20typo%20on%20one%20of%20my%20sheets%20and%20wrote%20%22Sept%202018%22.%20I%20noticed%20however%20it%26nbsp%3Bstill%20counted%20that%20occurence.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIn%20conculsion%2C%20the%20function%20above%20counts%20both%20%22Sep%202018%22%20and%20%22Sept%202018%22.%20What's%20going%20on%20here%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-89295%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-89332%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIFS%20or%20COUNTIFS%20Bug%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-89332%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F68257%22%20target%3D%22_blank%22%3E%40Perry%20Rakhra%3C%2FA%3E%20wrote%3A%3CBR%20%2F%3E%3CP%3EIn%20conculsion%2C%20the%20function%20above%20counts%20both%20%22Sep%202018%22%20and%20%22Sept%202018%22.%20What's%20going%20on%20here%3F%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EType%20%3CSTRONG%3ESep%202018%3C%2FSTRONG%3E%20in%20a%20cell%20and%20%3CSTRONG%3ESept%202018%3C%2FSTRONG%3E%20in%20another%20cell.%20Both%20will%20be%20converted%20to%20%3CSTRONG%3E01.09.2018%3C%2FSTRONG%3E%20shown%20as%20%3CSTRONG%3ESep%202018%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EAnd%20here%20is%20the%20catch%3A%20COUNTIF()%20also%20does%20type%20converting%20(text%20to%20number).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I've been using a SUMIF and COUNTIF function to count the number of times months occur. My reference formula is this:

 

=COUNTIF('PNL'!$F2:$F189,"Sep 2018")+COUNTIF('Misc'!$F2:$F189,"Sep 2018")

 

I made a typo on one of my sheets and wrote "Sept 2018". I noticed however it still counted that occurence.


In conculsion, the function above counts both "Sep 2018" and "Sept 2018". What's going on here?

1 Reply
Highlighted

Hi

 


@Perry Rakhra wrote:

In conculsion, the function above counts both "Sep 2018" and "Sept 2018". What's going on here?


Type Sep 2018 in a cell and Sept 2018 in another cell. Both will be converted to 01.09.2018 shown as Sep 2018.

And here is the catch: COUNTIF() also does type converting (text to number).