SOLVED

Issue with NB.SI.ENS (french equivalent to COUNTIFS) function and reference

%3CLINGO-SUB%20id%3D%22lingo-sub-2318185%22%20slang%3D%22fr-FR%22%3EIssue%20with%20NB.SI.%20ENS%20(french%20equivalent%20to%20COUNTIFS)%20function%20and%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2318185%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EI%20have%20a%20table%2C%20that%20is%20a%20reference%20to%20annother%20one%20except%20the%20empty%20cells%20stay%20empty%20(thanks%20to%20an%20IF%20function).%3C%2FP%3E%3CP%3EFor%20the%20%22Source%20table%22%3C%2FP%3E%3CUL%3E%3CLI%3EWhen%20i%20try%20to%20count%20the%20number%20of%20cells%20in%20the%20column%20%22Periode%20average%22%20that%20are%20not%20empty%20and%20that%20are%20corresponding%20to%20a%20%22Non%20Sportif%20line%22%20it%20works%20perfectly%20with%20the%20function%3A%3C%2FLI%3E%3CLI%3EI'NB.SI.%20ENS%20(I2%3AI6%3B%22%20Non-Sporting%22%3B%20K2%3AK6%3B%22%20%26lt%3B%26gt%3B%20%22)%3C%2FLI%3E%3CLI%3EWhich%20is%20the%20French%20equivalent%20to%20%22%20'COUNTIFS(I2%3AI6%3B%22%20Non-Sporting%22%3B%20K2%3AK6%3B%22%20%26lt%3B%26gt%3B%20%22)%20%22%3C%2FLI%3E%3CLI%3EThat%20returns%20me%203%20the%20correct%20answer%3C%2FLI%3E%3C%2FUL%3E%3CP%3EBut%20when%20i%20do%20the%20same%20thing%20on%20a%20table%20that%20is%20refering%20to%20the%20%22Source%20table%22%3C%2FP%3E%3CUL%3E%3CLI%3EI'NB.SI.%20ENS%20(A13%3AA17%3B%22%20Non-Sporting%22%3B%20C13%3AC17%3B%22%20%26lt%3B%26gt%3B%20%22)%3C%2FLI%3E%3CLI%3EEquivalent%20to%20'COUNTIFS(A13%3AA17%3B%22%20Non-Sporting%22%3B%20C13%3AC17%3B%22%20%26lt%3B%26gt%3B%20%22)%3C%2FLI%3E%3CLI%3EThat%20returns%20me%204%20(counting%20the%20C16%20cell%20as%200)%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20COUNTIFS%20function%20applied%20on%20the%20reference%20table%20count%20the%20empty%20cell%20as%20if%20it%20was%200%20and%20i%20would%20like%20it%20to%20return%20the%20same%20result%20as%20for%20the%20souce%20table%2C%20can%20anyone%20help%20me%20please%3F%3C%2FP%3E%3CP%3EHere%20is%20a%20screenshot%20for%20you%20to%20understand%20better%20and%20the%20excel%20file.%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture%20excel%20countifs.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277503iA1D8D9B2DAA01BAC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture%20excel%20countifs.PNG%22%20alt%3D%22Capture%20excel%20countifs.%20PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2318185%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2318412%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20NB.SI.ENS%20(french%20equivalent%20to%20COUNTIFS)%20function%20and%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2318412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043858%22%20target%3D%22_blank%22%3E%40Wisswiss%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUne%20cellule%20avec%20une%20formule%20n'est%20pas%20vraiment%20vide%2C%20m%C3%AAme%20si%20la%20formule%20renvoie%20%22%22.%20Utiliser%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSOMMEPROD((A13%3AA17%3D%22Non%20Sportif%22)*(C13%3AC17%26lt%3B%26gt%3B%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!

I have a table, that is a reference to annother one except the empty cells stay empty (thanks to an IF function).

For the "Source table"

  • When i try to count the number of cells in the column "Periode moyenne" that are not empty and that are corresponding to a "Non Sportif line" it works perfectly with the function: 
  • =NB.SI.ENS(I2:I6;"Non Sportif";K2:K6;"<>") 
  • Which is the French equivalent to " =COUNTIFS(I2:I6;"Non Sportif";K2:K6;"<>") "
  • That returns me 3 the correct answer

But when i do the same thing on a table that is refering to the "Source table"

  • =NB.SI.ENS(A13:A17;"Non Sportif";C13:C17;"<>")
  • Equivalent to =COUNTIFS(A13:A17;"Non Sportif";C13:C17;"<>")
  • That returns me 4 (counting the C16 cell as 0)

 

So the COUNTIFS function applied on the reference table count the empty cell as if it was 0 and i would like it to return the same result as for the souce table, can anyone help me please?

Here is a screenshot for you to understand better and the excel file.

Thank you in advanceCapture excel countifs.PNG

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

@Wisswiss 

Une cellule avec une formule n'est pas vraiment vide, même si la formule renvoie "". Utiliser

 

=SOMMEPROD((A13:A17="Non Sportif")*(C13:C17<>""))

@Hans Vogelaar Merci pour votre réponse! Cela a fonctionné, et j'ai aussi appris une nouvelle fonction par la meme occasion!