+ Reply to Thread
Results 1 to 6 of 6

Formula to add one if cell isn't blank

  1. #1
    Forum Contributor
    Join Date
    06-28-2009
    Location
    newcastle, uk
    MS-Off Ver
    Excel 2010
    Posts
    171

    Formula to add one if cell isn't blank

    Hi guys

    I have attached a file which shows what I am looking for.

    Basically it is a table in which I have a column for how many wins a virtual horse started on when I bought it. Then it has a column for how much profit or loss I have made for each win I get since I bought the horse.

    What I would like is a formula in the column 'wins now' (B2) which adds up wins before from cell A2 + if Cells C2:D2 (could eventually go to C2:V2) aren't blank, add one for each.

    Thanks in advance
    Gemma
    Attached Files Attached Files

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula to add one if cell isn't blank

    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,545

    Re: Formula to add one if cell isn't blank

    Maybe:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Formula to add one if cell isn't blank

    So I came up with two solutions.
    Right now I've got C2:D2 as the range, but you can change that to C2:V2 no problem if you want.

    1) Array Formula
    {=SUM(A2,IF(ISBLANK(C2:D2),0,1))}
    enter with CTRL+SHIFT+ENTER not just enter

    2) COUNTIFS
    =A2 + COUNTIFS(C2:D2, ">0")
    Note this counts which cells have a number that's more than zero, not for empty cells. So if you have a text string in one of those then it won't count it.

    Well, the countifs is probably better, it's just that the array formula occured to me first.

  5. #5
    Forum Contributor
    Join Date
    06-28-2009
    Location
    newcastle, uk
    MS-Off Ver
    Excel 2010
    Posts
    171

    Re: Formula to add one if cell isn't blank

    Thanks for all the solutions guys, greatly appreciated.

    Gemma

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,545

    Re: Formula to add one if cell isn't blank

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  2. [SOLVED] Cell referenced in formula has no information displayed (shows blank), return blank cell
    By nunayobinezz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-07-2013, 04:51 PM
  3. Mac Excel: Hiding rows if cell blank, when being blank depends on a conditional formula
    By lee_5_5 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 06-06-2013, 11:25 PM
  4. [SOLVED] Need formula to return blank instead of #N/A when source cell is blank, using VLOOKUP
    By TMB1234 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2013, 07:16 PM
  5. I want a formula cell to be blank if cells computing formula are blank
    By asawyer3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2011, 05:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1