NB IF with empty cells
Solved
Anthelm
Posted messages
202
Status
Membre
-
Raymond PENTIER Posted messages 58551 Registration date Status Contributeur Last intervention -
Raymond PENTIER Posted messages 58551 Registration date Status Contributeur Last intervention -
Hello,
Everything is in the title and in the file! I tried to explain my problem visually in the file.
In fact, when using a COUNTIFS function in a table, I have an issue with my empty cells.
Thank you so much for your help!
https://www.cjoint.com/c/HKfwQkl3MhL
Everything is in the title and in the file! I tried to explain my problem visually in the file.
In fact, when using a COUNTIFS function in a table, I have an issue with my empty cells.
Thank you so much for your help!
https://www.cjoint.com/c/HKfwQkl3MhL
3 réponses
Hello
Instead of referencing a cell for the empty value, put it directly in the formula and it works
=COUNTIFS($A$2:$A$7,H3,$B$2:$B$7,I3,$C$2:$C$7,J3,$D$2:$D$7,K3,$E$2:$E$7,"")
or you can concatenate each row and use COUNTIF
https://www.cjoint.com/c/HKfxzDWEEsh
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
Instead of referencing a cell for the empty value, put it directly in the formula and it works
=COUNTIFS($A$2:$A$7,H3,$B$2:$B$7,I3,$C$2:$C$7,J3,$D$2:$D$7,K3,$E$2:$E$7,"")
or you can concatenate each row and use COUNTIF
https://www.cjoint.com/c/HKfxzDWEEsh
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
Good evening (and hi friend Via)
to try (I say: well to try) in N2 to pull on height:
=SUMPRODUCT(($A$2:$A$8&$B$2:$B$8&$C$2:$B$8&$D$2:$D$8&$E$2:$E$8=H2&I2&J2&K2&L2)*1)
it gives this on the model, to see if it works in all cases:
https://mon-partage.fr/f/2EzqzAiE/
best regards
to try (I say: well to try) in N2 to pull on height:
=SUMPRODUCT(($A$2:$A$8&$B$2:$B$8&$C$2:$B$8&$D$2:$D$8&$E$2:$E$8=H2&I2&J2&K2&L2)*1)
it gives this on the model, to see if it works in all cases:
https://mon-partage.fr/f/2EzqzAiE/
best regards
Good evening and thank you Vaucluse!
Thanks to Via, I was able to find a solution:
A column with:
=[@Height]&" "&[@Width]&" "&[@[Criterion 3]]&" "&[@[Criterion 4]]&" "&[@[Criterion 5]]
Which "groups" all the criteria into one cell. Separated by spaces to differentiate the columns after concatenation (Otherwise A BC would be the same as (AB C)
So in this column that I call "concatenate" I have duplicates that will appear.
I create another column:
=COUNTIF([Concatenate];[@Concatenate])
I know the number of duplicates! :)
The only thing that bothers me now is the blank rows, which count as an element. If I have 4 blank rows in the table, they appear with a 4... If anyone has an idea to fix this, that would be nice but it's not blocking...
Thanks again
Thanks to Via, I was able to find a solution:
A column with:
=[@Height]&" "&[@Width]&" "&[@[Criterion 3]]&" "&[@[Criterion 4]]&" "&[@[Criterion 5]]
Which "groups" all the criteria into one cell. Separated by spaces to differentiate the columns after concatenation (Otherwise A BC would be the same as (AB C)
So in this column that I call "concatenate" I have duplicates that will appear.
I create another column:
=COUNTIF([Concatenate];[@Concatenate])
I know the number of duplicates! :)
The only thing that bothers me now is the blank rows, which count as an element. If I have 4 blank rows in the table, they appear with a 4... If anyone has an idea to fix this, that would be nice but it's not blocking...
Thanks again
In fact, I could solve the problem by ensuring that the cells always return a value (for example, 0 but ideally that they remain visually empty) so that the COUNTIFS function can find it.
Hello Anthelm.
You're asking if it's possible ... No!
Not with explanations that don't match the tables:
1) You state "It is possible that my columns may not all be filled (for example columns 5 and 8)"; but it seems it would rather be columns 4 and 5, right?
2) In your formulas in column N, you are working on rows 2 to 7; what happens to row 8 and its content?
3) Nowhere do you clearly express what the objective of this file is: To find the number of identical rows? So why worry about empty cells? And why complicate things with the COUNTIFS function?
Here’s a solution with concatenation in G, without duplicates in I and counting in J: https://www.cjoint.com/c/HKgr2hCtj2b
You're asking if it's possible ... No!
Not with explanations that don't match the tables:
1) You state "It is possible that my columns may not all be filled (for example columns 5 and 8)"; but it seems it would rather be columns 4 and 5, right?
2) In your formulas in column N, you are working on rows 2 to 7; what happens to row 8 and its content?
3) Nowhere do you clearly express what the objective of this file is: To find the number of identical rows? So why worry about empty cells? And why complicate things with the COUNTIFS function?
Here’s a solution with concatenation in G, without duplicates in I and counting in J: https://www.cjoint.com/c/HKgr2hCtj2b
I will try to fix that tomorrow, but I think I will go with the second option.