Forum Discussion
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
=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).
3 Replies
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.
=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).
- Thank you very much for your answer, it works perfectly.
Have a good day
