View original (French)

NB IF with empty cells

Solved
Anthelm Posted messages 202 Status Member -  
Raymond PENTIER Posted messages 58213 Registration date   Status Contributor 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

3 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    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
    1
    1. Anthelm Posted messages 202 Status Member 1
       
      The first formula doesn't work on all the rows of the table :(

      I will try to fix that tomorrow, but I think I will go with the second option.
      0
    2. Anthelm Posted messages 202 Status Member 1
       
      I think that sometimes my last column contains a value, and sometimes the last two columns contain nothing for certain rows of the table. So it's messing up because it seems that your formula just... "skips" the last column...!
      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    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

    1
    1. Anthelm Posted messages 202 Status Member 1
       
      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
      0
  3. Anthelm Posted messages 202 Status Member 1
     
    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.
    0
    1. Anthelm Posted messages 202 Status Member 1
       
      Do you know if it's possible?
      0
    2. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      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
      0
    3. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      .. and here more directly, based on the tables
      https://mon-partage.fr/f/2EzqzAiE/
      thanks
      0
    4. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      Thank you! Simple and effective...
      Have a nice day.
      0