SOLVED

# Excel sum functions

Copper Contributor

# Excel sum functions

Hello everyone,

I need to make a counting table using datas from an other table like shown in the picture under. I've tried NB SI ENS formulas with for exemple : =NB si ens (\$D\$6:\$D\$29;H5=D7; \$E\$6:\$E\$29;E7=G6) but it doesn't work. It's in reality a big table with > 250 columns and > 20 lines to fill.

Would you have an idea on how to make it with a single formula please ?

Edit : NB SI is the french equivalent of COUNT IF

Thank you very much,

Arnaud

3 Replies
best response confirmed by ArnaudL360 (Copper Contributor)
Solution

# Re: Excel sum functions

=NB.SI.ENS(\$D\$7:\$D\$29;H\$5;\$E\$7:\$E\$29;\$G6)

This formula works in the attached sample file. Please note that the IDs in the file are always entered without a space for example ID1 in cell H5. In your screenshot there is for example ID 1 in cell H5 which wouldn't work because it doesn't match the entries in column D (ID column).

# Re: Excel sum functions

Example

Suppose you have a table like this in your Excel sheet:

 Category Status Apple Sold Banana Available Apple Sold Banana Sold Apple Available

And you want to count occurrences in another table:

 Sold Available Apple Banana

To fill in the values, use the formula in the appropriate cells of your counting table:

(French Formulas)

For "Apple" and "Sold":

• Cell (H5 = "Apple", I5 = "Sold"):

=NB.SI.ENS(\$A\$2:\$A\$6, "Apple", \$B\$2:\$B\$6, "Sold")

For "Banana" and "Available":

• Cell (H6 = "Banana", I6 = "Available"):

=NB.SI.ENS(\$A\$2:\$A\$6, "Banana", \$B\$2:\$B\$6, "Available")

AutoFill Formula:

Once the formulas are set for the first cell, you can drag the formulas across and down to auto-fill the remaining cells.

By using the COUNTIFS function properly, you can count based on multiple criteria in your data set efficiently. The text was created with the help of AI.

If you want to translate the formulas goes here: Excel-Translator Français

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

# Re: Excel sum functions

Thank you very much for your answer, it works perfectly.
Have a good day
1 best response

Accepted Solutions
best response confirmed by ArnaudL360 (Copper Contributor)
Solution

# Re: Excel sum functions

=NB.SI.ENS(\$D\$7:\$D\$29;H\$5;\$E\$7:\$E\$29;\$G6)

This formula works in the attached sample file. Please note that the IDs in the file are always entered without a space for example ID1 in cell H5. In your screenshot there is for example ID 1 in cell H5 which wouldn't work because it doesn't match the entries in column D (ID column).