+ Reply to Thread
Results 1 to 9 of 9

Help Creating a SUMIFS (Running Total) Formula

  1. #1
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Help Creating a SUMIFS (Running Total) Formula

    Can anyone help me write a formula that will find the value of cells in column L?

    I'd like to write a formula that will keep a running total (sumifs) of column K values IF: C="@*", M>=N*3.

    An example of this formula working properly looks like this example:

    C------K------L------M------N
    @AB--(100)-(100)---(0)-----(0)
    @AC--(105)-(205)---(1)-----(0)
    @AD--(110)-(325)---(2)-----(0)
    GH----(100)-(325)---(2)-----(0)
    @TB--(-120)-(205)--(2)-----(1)
    @RH--(100)--(205)--(3)-----(1)
    @VC--(100)--(305)--(4)-----(1)
    Attached Files Attached Files

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Help Creating a SUMIFS (Running Total) Formula

    What is wrong with the formula that you already have in column L?

  3. #3
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Help Creating a SUMIFS (Running Total) Formula

    Do you wish this:
    =SUMPRODUCT(K$1:K3,(LEFT(C$1:C3,1)="@")*($M$1:M3>=$N$1:N3*3))

  4. #4
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Help Creating a SUMIFS (Running Total) Formula

    Izandol, your formula is VERY close to what I'm looking for. The point of contention exists in K94 & K111. I would like the PREVIOUS row's information to determine the formula. Attached is a revised version of the file with how I would like the formula's final product to look like. I highlighted the problem areas in grey.
    Attached Files Attached Files

  5. #5
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Help Creating a SUMIFS (Running Total) Formula

    I am not sure I understand fully. Can you explain the exact logic for row 111 in words - especially which criteria depend on which rows? (do you wish to consider all rows prior to the formula row or just the one row above?)

    Perhaps this:
    L2: =IF(AND(LEFT(C$2,1)="@",$M$2>=$N$2*3),N(K2),0)
    L3: =SUMPRODUCT(K$2:K3,(LEFT(C$2:C3,1)="@")*($M$1:M2>=$N$1:N2*3))
    then you may fill L3 down
    Last edited by Izandol; 11-28-2013 at 01:13 PM.

  6. #6
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Help Creating a SUMIFS (Running Total) Formula

    The logic is this:

    L5: 1) C5 contains "@*", 2) M4>=N4*3 therefore: sum value of K5 105.

    L21: 1) C5 contains "@*", 2) M20>=N20*3 therefore: sum value of K21 120.

    .............
    .............
    .............


    L111: 1) C111 contains "@*", 2) M110<>>=N100*3 therefore: do not sum value of K111

  7. #7
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Help Creating a SUMIFS (Running Total) Formula

    I was able to make the formula work, but I couldn't have done it without your help.

    ONE LAST question:

    Can you add a criteria to the formula? Can you make it so that the formula does the exact same thing only to make it so that the sum of N & M has to be >= 1 as well?
    Attached Files Attached Files

  8. #8
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Help Creating a SUMIFS (Running Total) Formula

    Do you mean this:
    =SUMPRODUCT(K$2:K4,(LEFT(C$2:C4,1)="@")*($M$1:M3>=$N$1:N3*3)*($M$1:M3+$N$1:N3>=1))

  9. #9
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Help Creating a SUMIFS (Running Total) Formula

    Remarkable! Thanks.

+ 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. Creating a running total.
    By richimpulse in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-19-2013, 09:22 AM
  2. Creating a running total for a budget
    By venetian_jigsaw in forum Excel General
    Replies: 2
    Last Post: 07-11-2011, 02:34 PM
  3. Replies: 1
    Last Post: 11-22-2010, 01:52 PM
  4. Creating a Daily Running Total Query
    By theseekeroftru7h in forum Access Tables & Databases
    Replies: 0
    Last Post: 08-12-2009, 07:56 AM
  5. [SOLVED] Running total formula
    By DBane in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-02-2006, 02:45 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