Microsoft Office Forums Count formula help. Replace ZERO with "-"

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-25-2019, 05:26 PM
baes10 baes10 is offline Count formula help. Replace ZERO with "-" Windows 7 64bit Count formula help. Replace ZERO with "-" Office 2007
Advanced Beginner
Count formula help. Replace ZERO with "-"
 
Join Date: Dec 2017
Posts: 32
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, 10 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's Avatar
Marcia Marcia is offline Count formula help. Replace ZERO with "-" Windows 7 32bit Count formula help. Replace ZERO with "-" Office 2013
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 208
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 Count formula help. Replace ZERO with "-" Windows 10 Count formula help. Replace ZERO with "-" Office 2019
Advanced Beginner
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 30
Alansidman is on a distinguished road
Default

Change the format to Accounting.
Attached Images
File Type: jpg Capture.JPG (46.0 KB, 9 views)
Reply With Quote
  #4  
Old 04-25-2019, 06:58 PM
Marcia's Avatar
Marcia Marcia is offline Count formula help. Replace ZERO with "-" Windows 7 32bit Count formula help. Replace ZERO with "-" Office 2013
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 208
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
Count formula help. Replace ZERO with "-" 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
Count formula help. Replace ZERO with "-" How to replace e-mail address with "Dispaly as:" name of the Outlook Contact. RobertF Mail Merge 1 06-30-2013 09:54 AM
Count formula help. Replace ZERO with "-" 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
Count formula help. Replace ZERO with "-" 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 04:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft