SOLVED

New Contributor

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

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.

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

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

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

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

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

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