Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-25-2019, 05:26 PM
baes10 baes10 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Dec 2017
Posts: 29
baes10 is on a distinguished road
Default 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
File Type: jpg Formula.JPG (223.9 KB, 9 views)
Attached Files
File Type: xlsm Community CSR.xlsm (152.5 KB, 1 views)
Reply With Quote
  #2  
Old 04-25-2019, 06:16 PM
Marcia Marcia is offline Windows 7 32bit Office 2013
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 182
Marcia is on a distinguished road
Default

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
File Type: xlsm Copy of Community CSR-1.xlsm (147.0 KB, 0 views)
Reply With Quote
  #3  
Old 04-25-2019, 06:17 PM
Alansidman's Avatar
Alansidman Alansidman is offline Windows 10 Office 2019
Novice
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 15
Alansidman is on a distinguished road
Default

Change the format to Accounting.
Attached Images
File Type: jpg Capture.JPG (46.0 KB, 8 views)
Reply With Quote
  #4  
Old 04-25-2019, 06:58 PM
Marcia Marcia is offline Windows 7 32bit Office 2013
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 182
Marcia is on a distinguished road
Default

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

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 10:31 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft