SOLVED

COUNTIFS LESS THAN YEAR 2017 does not work when I use "<"&2017

%3CLINGO-SUB%20id%3D%22lingo-sub-1481554%22%20slang%3D%22en-US%22%3ECOUNTIFS%20LESS%20THAN%20YEAR%202017%20does%20not%20work%20when%20I%20use%20%22%26lt%3B%22%26amp%3B2017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1481554%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20COUNTIFS%20less%20than%20year%202017%20formula%20does%20not%20work%2C%20please%20see%20below%20and%20in%20attached%20excel%20the%20cell%20highlighted%20in%20yellow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(Indirect!AI%3AAI%2C%22Agent%22%2CIndirect!AQ%3AAQ%2C%22%26lt%3B%22%26amp%3B2017%2CIndirect!AR%3AAR%2C%220%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJenny%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1481554%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1481592%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20LESS%20THAN%20YEAR%202017%20does%20not%20work%20when%20I%20use%20%22%26lt%3B%22%26amp%3B2017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1481592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20check%20the%20file%2C%20but%20it%20shall%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(Indirect!AI%3AAI%2C%22Agent%22%2CIndirect!AQ%3AAQ%2C%22%26lt%3B2017%22%2CIndirect!AR%3AAR%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1482918%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20LESS%20THAN%20YEAR%202017%20does%20not%20work%20when%20I%20use%20%22%26lt%3B%22%26amp%3B2017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1482918%22%20slang%3D%22en-US%22%3E%3CP%3ESadly%20I%20tried%20that%20first%20and%20it%20did%20not%20work%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483071%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20LESS%20THAN%20YEAR%202017%20does%20not%20work%20when%20I%20use%20%22%26lt%3B%22%26amp%3B2017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483071%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20was%20in%20shortage%20of%20time%20yesterday%20to%20check%20the%20file%20carefully.%3C%2FP%3E%0A%3CP%3EYou%20keep%20years%20%3CSTRONG%3EYears%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EEC%20previously%3F%3C%2FSTRONG%3E%20flag%20as%20texts%2C%20not%20as%20numbers.%20With%20using%20COUNIFS%20it%20always%20converts%20criteria%20parameter%20from%20text%20to%20number%20if%20text%20represents%20number%20(or%20date).%20Thus%20you%20compare%20as%20%3CSTRONG%3E%222007%22%20%26lt%3B%202007%3C%2FSTRONG%3E%20and%20since%20any%20text%20is%20always%20%22more%22%20than%20any%20number%20we%20have%20zero%20result.%20The%20workaround%20is%20to%20add%20soften%20hyphen%20before%20such%20parameters.%20Thus%20the%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(Indirect!AI%3AAI%2C%22Agent%22%2C%0A%20%20%20%20%20%20%20%20%20%20Indirect!AQ%3AAQ%2C%22%26lt%3B%22%26amp%3BCHAR(173)%26amp%3B%222017%22%2C%0A%20%20%20%20%20%20%20%20%20%20Indirect!AR%3AAR%2CCHAR(173)%26amp%3B%220%22%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAnother%20option%20is%20to%20use%20SUMPRODUCT()%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483090%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20LESS%20THAN%20YEAR%202017%20does%20not%20work%20when%20I%20use%20%22%26lt%3B%22%26amp%3B2017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483090%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BCan%20I%20ask%20did%20you%20apply%20any%20formatting%20to%20any%20of%20the%20cells%20used%20in%20the%20formula%3F%20As%20when%20I%20copy%20your%20formula%20across%20into%20my%20latest%20version%20excel%20I%20still%20get%200%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483103%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20LESS%20THAN%20YEAR%202017%20does%20not%20work%20when%20I%20use%20%22%26lt%3B%22%26amp%3B2017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483103%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENope%2C%20I%20didn't%20re-format%20the%20data.%3C%2FP%3E%0A%3CP%3EYou%20may%20try%20alternative%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(%20(Indirect!AI%3AAI%3D%22Agent%22)*(Indirect!AQ%3AAQ%26lt%3B%222017%22)*(Indirect!AR%3AAR%3D%220%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eit's%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20285px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F200430i87409B4215076A17%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eplus%20please%20check%20if%20in%20main%20file%20you%20have%20texts%20or%20numbers%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20197px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F200431i99CC7B28AB43FB74%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1486024%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20LESS%20THAN%20YEAR%202017%20does%20not%20work%20when%20I%20use%20%22%26lt%3B%22%26amp%3B2017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1486024%22%20slang%3D%22en-US%22%3EThank%20you!%20Much%20appreciated!!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1486163%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20LESS%20THAN%20YEAR%202017%20does%20not%20work%20when%20I%20use%20%22%26lt%3B%22%26amp%3B2017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1486163%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hello,

 

My COUNTIFS less than year 2017 formula does not work, please see below and in attached excel the cell highlighted in yellow.

 

=COUNTIFS(Indirect!AI:AI,"Agent",Indirect!AQ:AQ,"<"&2017,Indirect!AR:AR,"0")

 

Thanks!

 

Jenny

7 Replies
Highlighted

@JennySommet 

I didn't check the file, but it shall be like

=COUNTIFS(Indirect!AI:AI,"Agent",Indirect!AQ:AQ,"<2017",Indirect!AR:AR,0)
Highlighted

Sadly I tried that first and it did not work  @Sergei Baklan 

Highlighted

@JennySommet 

Sorry, was in shortage of time yesterday to check the file carefully.

You keep years Years and EC previously? flag as texts, not as numbers. With using COUNIFS it always converts criteria parameter from text to number if text represents number (or date). Thus you compare as "2007" < 2007 and since any text is always "more" than any number we have zero result. The workaround is to add soften hyphen before such parameters. Thus the formula could be

=COUNTIFS(Indirect!AI:AI,"Agent",
          Indirect!AQ:AQ,"<"&CHAR(173)&"2017",
          Indirect!AR:AR,CHAR(173)&"0"
)

Another option is to use SUMPRODUCT()

Highlighted

Thank you! @Sergei Baklan Can I ask did you apply any formatting to any of the cells used in the formula? As when I copy your formula across into my latest version excel I still get 0

Highlighted
Best Response confirmed by JennySommet (Contributor)
Solution

@JennySommet 

Nope, I didn't re-format the data.

You may try alternative formula

=SUMPRODUCT( (Indirect!AI:AI="Agent")*(Indirect!AQ:AQ<"2017")*(Indirect!AR:AR="0"))

it's here

image.png

plus please check if in main file you have texts or numbers like

image.png

Highlighted
Thank you! Much appreciated!!!
Highlighted