Results 1 to 5 of 5

Thread: Adding number of times code appears in cells over a rolling period

  1. #1
    Board Regular
    Join Date
    Jul 2007
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding number of times code appears in cells over a rolling period

    I have a formula, found elsewhere, that counts the totals of cells from current date – backwards for one year
    =SUMIFS($L$5:$BDQ$5,$L$3:$BDQ$3,"<="&F37,$L$3:$BDQ$3,">="&DATE(YEAR(F37)-1,MONTH(F37),DAY(F37)))

    Data in $L$5:$BDQ$5
    Dates in ,$L$3:$BDQ$3
    Todays date in $F$37

    This works perfect for numerical values in the cells. However, I have a spread sheet that has “codes” as values in the cells: Two of the codes are “SO” & “S”. I need to count (sum) these as a rolling period from current date to one year previous
    Code used for COUNTIF.
    =COUNTIF(L5:BDQ5,"=SO")+COUNTIF(L5:BDQ5,"=S")

    Unfortunately I cannot work out how integrate the COUNTIF instead of the SUMIFS

    Probably just missing something simple? Any helps would be much appreciated

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,518
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Adding number of times code appears in cells over a rolling period

    I think it would be helpful to see an example of your data, along with your expected results.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,981
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Adding number of times code appears in cells over a rolling period

    Maybe...

    =SUM(COUNTIFS($L$5:$BDQ$5,{"S","SO"},$L$3:$BDQ$3,"<="&F37,$L$3:$BDQ$3,">="&DATE(YEAR(F37)-1,MONTH(F37),DAY(F37))))

    M.

  4. #4
    Board Regular
    Join Date
    Jul 2007
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Adding number of times code appears in cells over a rolling period

    Quote Originally Posted by Marcelo Branco View Post
    Maybe...

    =SUM(COUNTIFS($L$5:$BDQ$5,{"S","SO"},$L$3:$BDQ$3,"<="&F37,$L$3:$BDQ$3,">="&DATE(YEAR(F37)-1,MONTH(F37),DAY(F37))))

    M.
    Thank you very much Marcelo. Much appreciated.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,981
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Adding number of times code appears in cells over a rolling period

    You are welcome. Thanks for the feedback.

    M.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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