Microsoft Office Forums Count formula help. Replace ZERO with "-"
 Register FAQ Search Today's Posts Mark Forums Read

#1
04-25-2019, 05:26 PM
 baes10 Windows 7 64bit Office 2007 Advanced Beginner Join Date: Dec 2017 Posts: 32
Count formula help. Replace ZERO with "-"

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
Attached Images
 Formula.JPG (223.9 KB, 10 views)
Attached Files
 Community CSR.xlsm (152.5 KB, 1 views)
#2
04-25-2019, 06:16 PM
 Marcia Windows 7 32bit Office 2013 Competent Performer Join Date: May 2018 Location: Philippines Posts: 208

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.
Attached Files
 Copy of Community CSR-1.xlsm (147.0 KB, 0 views)
#3
04-25-2019, 06:17 PM
 Alansidman Windows 10 Office 2019 Advanced Beginner Join Date: Apr 2019 Location: Steamboat Springs Posts: 30

Change the format to Accounting.
Attached Images
 Capture.JPG (46.0 KB, 9 views)
#4
04-25-2019, 06:58 PM
 Marcia Windows 7 32bit Office 2013 Competent Performer Join Date: May 2018 Location: Philippines Posts: 208

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post ravl13 Excel 3 08-22-2017 12:26 PM audioman Word VBA 7 01-21-2016 03:11 PM RobertF Mail Merge 1 06-30-2013 09:54 AM krishnaoptif Word VBA 9 06-22-2012 05:08 AM Jamal NUMAN Word 2 07-03-2011 03:11 AM

All times are GMT -7. The time now is 04:25 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top