IF + COUNTIFS formula

[Sunshine8790]

I have a column that totals the amounts of 31 other columns based on value in those cells.
It works great, but there are so many rows of data that looking at a bunch of 0s for all the cells that have no data to return is eye boggling.

I'd like to edit the formula to include the "IF" function to return "" (blank) - or even "N/A" instead of 0 when there is no data.
I'm not sure how. I keep trying but I keep getting errors, so I'm looking for help.

This is my original formula I want to add the IF statement to:

=COUNTIFS(B5:AF5,"V")+COUNTIFS(B5:AF5,"P")+COUNTIFS(B5:AF5,"O")+COUNTIFS(B5:AF5,"L")+COUNTIFS(B5:AF5,"B")+COUNTIFS(B5:AF5,"J")+COUNTIFS(B5:AF5,"F")+COUNTIFS(B5:AF5,"M")+COUNTIFS(B5:AF5,"T")+COUNTIFS(B5:AF5,"H")/2

Anyone able to edit that for me to return a blank instead of a 0?
Much appreciated in advance.

 

[noveske]

Just set this in settings or conditional formatting. Doing by formula will have you updating all the formulas or update as you add more.

Hide or display all zero values on a worksheet

1. Click File > Options > Advanced.
2. Under Display options for this worksheet, select a worksheet, and then do one of the following:
   • To display zero (0) values in cells, check the Show a zero in cells that have zero value check box.
   • To display zero (0) values as blank cells, uncheck the Show a zero in cells that have zero value check box.

This is where you can have it display N/A if you want.

1. Select the cells that contain the zero (0) values that you want to hide.
2. You can press Ctrl+1, or on the Home tab, click Format > Format Cells.
   
   
3. Click Number > Custom.
4. In the Type box, type 0;-0;;@, and then click OK.

Or just update number format of all cells:

0;-0;;@

 

[Fluff]

I would go with cell formatting, but with a formula you can use

=IFERROR(1/(1/(SUM(COUNTIFS(B5:AF5,{"V","O","L","B","J","F","M","T"}))+COUNTIFS(B5:AF5,"H")/2)),"")

 

[Sunshine8790]

Just set this in settings or conditional formatting. Doing by formula will have you updating all the formulas or update as you add more.




This is where you can have it display N/A if you want.


Or just update number format of all cells:

0;-0;;@

Thank you noveske! This worked to get rid of the 0s.
Eventually I decided that the lack of any data looked incomplete, so I wound up using conditional formatting to just highlight all cells with a value greater than 0, but your solution did work for my original plan.

 

[Sunshine8790]

I would go with cell formatting, but with a formula you can use

=IFERROR(1/(1/(SUM(COUNTIFS(B5:AF5,{"V","O","L","B","J","F","M","T"}))+COUNTIFS(B5:AF5,"H")/2)),"")

Thank you Fluff
I am just seeing your response. I did end up using conditional formatting, so I no longer need the formula. Thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.