Count if

12/31/20
Accepted

=COUNTIFS({Equipment Inspection Log - Webform Range 4}, "Good", {Equipment Inspection Log - Webform Range 2}, [email protected])

What am I doing wrong? my first range is multiple columns

Best Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    My suggestion would be to add a column on the source sheet that counts how many cells are "Good" within the row and then in your other sheet the formula would be a SUMIFS that sums the counts in the helper column based on the description.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    You would use an IF statement to say that IF the formula = 0, blank, otherwise run formula.


    =IF(formula = 0, "", formula)

    =IF(MIN(COLLECT({Equipment Inspection Log - Webform Range 6}, {Equipment Service Log - Webform Range 2}, [email protected])) = 0, "", MIN(COLLECT({Equipment Inspection Log - Webform Range 6}, {Equipment Service Log - Webform Range 2}, [email protected])))

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Your range sizes and shapes must match, so if you have a range that is only a single column, then all ranges within the function must be a single column and the other way around. If you have multiple columns for a range then all ranges must be the same number of columns.


    If the range size is the issue, can you provide more detail as to your layout and what exactly you are trying to acomplish?

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    Im trying to count multiple columns where the cell is "Good" if the description range matches the cell

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    My suggestion would be to add a column on the source sheet that counts how many cells are "Good" within the row and then in your other sheet the formula would be a SUMIFS that sums the counts in the helper column based on the description.

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    I went to the sheet and created a % score for the the columns I wanted. Now I want to show the minimum score for the description row. Thoughts? I cant make it work


    =MIN(COLLECT({Equipment Inspection Log - Webform Range 6}), {Equipment Service Log - Webform Range 2}, [email protected])

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    It looks like you closed the COLLECT function too early. Move that closing parenthesis from after the first range to the end of the formula. That should get it working for you.

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    if it = 0, i want it to be blank, how do I make that happen?

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    I just want the 0% to be blank

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    You would use an IF statement to say that IF the formula = 0, blank, otherwise run formula.


    =IF(formula = 0, "", formula)

    =IF(MIN(COLLECT({Equipment Inspection Log - Webform Range 6}, {Equipment Service Log - Webform Range 2}, [email protected])) = 0, "", MIN(COLLECT({Equipment Inspection Log - Webform Range 6}, {Equipment Service Log - Webform Range 2}, [email protected])))

Sign In or Register to comment.