Need help leaving a cell blank if the formula has no sum value

Posted on

QUESTION :

I am using the below formula to calculate attendance and I would like the summary column to remain blank if there is no value entered from columns B12:AF12.

What am I missing to make this happen?

I tried putting "" on the outside of the last bracket and then closing everything but this is not working.

Below is my formula that I am using to sum up my row:

=COUNTIF(B12:AF12,"S")+(COUNTIF(B12:AF12,"S/")/2)

ANSWER :

Here is another option that keeps the repitition down:

=IFERROR(1/1/SUMPRODUCT(COUNTIF(B12:AF12,{"S","S","S/"})/2),"")

It does 3 countifs dividing the output by 2, but since S is counted twice it returns the actual count.

If the output of the sumproduct is 0 then we get an error as we try to divide by 0 and the IFERROR will output the ""

You would want to nest your COUNTIFS inside an IF statement, something like this could work:

IF(COUNTIF(B12:AF12,”S”)+COUNTIF(B12:AF12,”S/”)=0,””,COUNTIF(B12:AF12,”S”)+COUNTIF(B12:AF12,”S/”)/2)

When the IF statement evaluates to TRUE, the output is “” (blank), else the output is the formula you have specified.

Leave a Reply

Your email address will not be published.