Count formula help. Replace ZERO with "-"

baes10 · #1 04-25-2019, 05:26 PM

Hello, I've got a formula I need help in cell block F67, May Tab. Currently showing 0.00 but I need it to return a "-" if all of column G7:G64 is BLANK. Can you help adjust my formula?

Currently:
=IF(COUNTA(G7:G64)=0,"-",(COUNTIF(G7:G64,"A")*VLOOKUP("A",Cover!G68:H71,2 , FALSE)+COUNTIF(G7:G64,"B")*VLOOKUP("B",Cover!G68:H 71,2, FALSE)+COUNTIF(G7:G64,"F")*VLOOKUP("F",Cover!G68:H 71,2, FALSE))/COUNTA(G7:G64))

Much appreciated.
Steve

Marcia · #2 04-25-2019, 06:16 PM

Code:

=IF(COUNTA(G7:G64)="","-",(COUNTIF(G7:G64,"A")*VLOOKUP("A",Cover!G68:H71,2,   FALSE)+COUNTIF(G7:G64,"B")*VLOOKUP("B",Cover!G68:H71,2,   FALSE)+COUNTIF(G7:G64,"F")*VLOOKUP("F",Cover!G68:H71,2,   FALSE))/COUNTA(G7:G64))

Change the 0 to "" in your formula, after the =, then set the number format to accounting, symbol, none.

Alansidman · #3 04-25-2019, 06:17 PM

Change the format to Accounting.

Marcia · #4 04-25-2019, 06:58 PM

After editing the formula and setting the number format, F67 should show "-".

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formula to format "I" column based on if "F" column row is not blank	ravl13	Excel	3	08-22-2017 12:26 PM
Replace "comma" with "and" in line of text	audioman	Word VBA	7	01-21-2016 03:11 PM
How to replace e-mail address with "Dispaly as:" name of the Outlook Contact.	RobertF	Mail Merge	1	06-30-2013 09:54 AM
replace data from variable with "sub and super script" from excel to word by vba	krishnaoptif	Word VBA	9	06-22-2012 05:08 AM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM