Microsoft Office Forums Formula for Complete/Incomplete
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
08-09-2016, 05:07 AM
 Phil H Windows XP Office 2010 64bit Advanced Beginner Join Date: Jun 2010 Posts: 81
Formula for Complete/Incomplete

Need a formula in cell G29: if there is an X in any cell in the range H29:Z29, then Incomplete, else Complete. All cells being empty means complete.

=IF((H46:Z46)="x","incomplete","complete") does not work - returns #VALUE!.

Last edited by Phil H; 08-09-2016 at 12:49 PM. Reason: Added formula comment.
#2
08-10-2016, 06:52 AM
 Phil H Windows XP Office 2010 64bit Advanced Beginner Join Date: Jun 2010 Posts: 81
Partial Answer

This formula works for three cells. The range is 19 cells long, so how to condense this formula where all cells are included into one phrase?

=IF(H46="X","Incomplete",IF(I46="X","Incomplete",I F(J46="X","Incomplete","Complete")))
#3
08-10-2016, 11:21 AM
 NoSparks Windows 7 64bit Office 2010 64bit Excel Hobbyist Join Date: Nov 2013 Location: British Columbia, Canada Posts: 779

try this in G29 and drag down

=IF(COUNTIF(\$H29:\$Z29,"x")>0,"Incomplete",IF(COUNT IF(\$H29:\$Z29,"")=19,"Complete","Incomplete"))

EDIT: I can't get rid of the space in the second COUNTIF
#4
08-10-2016, 12:21 PM
 Phil H Windows XP Office 2010 64bit Advanced Beginner Join Date: Jun 2010 Posts: 81

Thanks NoSparks - works exactly as needed. I toyed around with the count thing but couldn't figure it out. You've saved me a lot of time and aggravation.

 Tags formula, range formula

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post zislam14 Project 3 12-17-2015 03:03 PM patrickd123 Project 1 04-07-2015 08:15 AM jirado Word 1 11-09-2014 01:32 PM ketanco Project 1 06-16-2014 11:08 AM gsandle Office 0 07-16-2013 11:51 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:56 PM.

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

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