+ Reply to Thread
Results 1 to 8 of 8

How to count consecutive occurances of values greater than 0?

  1. #1
    Registered User
    Join Date
    05-24-2012
    Location
    Washington, DC, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    How to count consecutive occurances of values greater than 0?

    I'm a fundraiser for a charity. I have an excel sheet containing our individual donors' giving totals for each year--going back 15 years. Of course, few of our donors have donated every year. If the a donor did not give in a certain year, their giving total for that year is shown as 0.

    I am in desperate need of a formula that will count the consecutive years a donor has given $1 or more to our organization, counting backwards from the year 2011.

    So...
    If the donor gave every year for the past fifteen years, the formula will return 15.
    If the donor gave only one gift, and the gift was in 2011, the formula will return 1.
    If the donor did not give in 2011, the formula will return 0.
    If the donor gave in 2011 and 2010, but not 2009, the formula will return 2.
    If the donor gave in 14 of the last 15 years, but didn't give in 2008, the formula will return 3.

    I am using Microsoft Excel 2010.

    Attached is a sample of my data.

    THANK YOU in advance for your help!!!
    Attached Files Attached Files

  2. #2
    Forum Expert 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: How to count consecutive occurances of values greater than 0?

    Hi and welcome to the forum.

    Do you mean this? In Q2 and copy down.

    =COUNTIF(B2:P2,">0")
    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 MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: How to count consecutive occurances of values greater than 0?

    Hi Ihorner and welcome to the forum.

    The match function should work for you. See the attached..
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    05-24-2012
    Location
    Washington, DC, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to count consecutive occurances of values greater than 0?

    Hi Fotis1991,

    Thanks for your reply.

    The "Countif" function is part of the solution, but in addition to counting if value is greater than zero, I need the count to begin with the left-most cell, then count to the right each cell with a value greater than zero, until it hits a cell with 0, then stop counting.

  5. #5
    Forum Expert 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: How to count consecutive occurances of values greater than 0?

    So, Marvin's suggestion, must works for you!

  6. #6
    Registered User
    Join Date
    05-24-2012
    Location
    Washington, DC, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to count consecutive occurances of values greater than 0?

    Yes, exactly, thank you for your help!

  7. #7
    Registered User
    Join Date
    05-24-2012
    Location
    Washington, DC, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to count consecutive occurances of values greater than 0?

    Yes, that was EXACTLY what I was looking for! THANK YOU!!!

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: How to count consecutive occurances of values greater than 0?

    Hi,

    Here is the answer workbook a second time to see if it is downloadable now.
    15 minus first zero.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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