Running Total Based On Criteria

    I would like to know if it is possible to have a cell calculate a running total based on previous data as soon as you enter new information. The spreadsheet is a log of suspensions. Relevant columns include the student's name, date, and length of suspension (in days). For example, if Column A= Name (could be separated into first and last name if necessary, or merged into one column), B= Date, C=# of Days, D= Reason and so on, can Excel add the number of total days of suspension for all violations as soon as a new record is created? (If a student has already been suspended for one day, then gets suspended a month later for something else for three days, can excel tell you immediately how many days that student has been suspended total?)


    This is actually a question from my mom, who asked me to help her, and who told me that a former coworker's wife had created a spreadsheet that could do this. Unfortunately, that spreadsheet no longer exists and they must recreate something.


    Thanks for any advice!

    Re: Running Total Based On Specific Criteria


    You could have a column that would show the total days suspensions for the student on that row, up until that row, i.e. in row 2 in an unused column


    =SUMIF(A$2:A2,A2,C$2:C2)


    copied down

    Re: Running Total Based On Specific Criteria


    But doesn't that code have to specify a specific student? (eg, if A2= John B., then it would sum all of his suspensions, but not anyone else's). And what if someone new was entered into the system? Would a new formula have to be created?


    I want to create something that will be very user-friendly and doesn't require them to create new formulas, etc.

    Re: Running Total Based On Specific Criteria


    Quote from Luna

    But doesn't that code have to specify a specific student? (eg, if A2= John B., then it would sum all of his suspensions, but not anyone else's).

    No.

    Quote from Luna

    And what if someone new was entered into the system? Would a new formula have to be created?

    Not if you switch this facility on:
    [COLOR="DarkSlateBlue"]Extend formats and formulas to additional rows
    By default, Microsoft Excel automatically formats new data that you type at the end of a range to match the preceding rows. Excel also automatically copies formulas that have been repeated in the preceding rows and extends them to additional rows.


    Note In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows.


    You can turn this option off (or back on again) at any time:


    On the Tools menu, click Options.
    On the Edit tab, clear the Extend data range formats and formulas check box to turn automatic formatting off.
    To turn automatic formatting back on again, select the Extend data range formats and formulas check box.[/COLOR]
    p45cal

    Re: Running Total Based On Specific Criteria


    DLL is offline, so if I may, regarding his formula:


    Quote

    (eg, if A2= John B., then it would sum all of his suspensions, but not anyone else's).


    Yes, it would sum all his suspensions, and no one else's.


    Here's the function:


    =SUMIF(A$2:A2, A2, C$2:C2)


    Suppose you copy it down for a bunch of rows. Then the formula's references where it appears in, say, row 10, have changed (automatically when you copied the formula) to


    =SUMIF(A$2:A10, A10, C$2:C10)


    The formula says, "compare the name in A10 to all the names from A2:A10. Add the values in C2:C10 where the names match." Those are the total suspensions for that student, and that student only.


    What P45 is telling you is that once you've copied the formula down several rows, and then you enter a new name (a new record), Excel will automatically replicate the formula in the new row.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Running Total Based On Specific Criteria


    Thank you for clarifying. Is there any way to base the formula on the new value being entered? For instance, if John B. is entered, to immediately tell you his total? As opposed to having to specificy a certain student/cell. (Because, if I understand correctly, that means that the user would have to modify the formula's criteria each time they wanted a different student's total). If I was the end user, the above formula would be fine, but I don't think the end user of this spreadsheet will know how to modify formulas.


    Thank you for your suggestions, advice, and help! (I am leaving work right now so won't check any replies until tomorrow morning.)

    Re: Running Total Based On Specific Criteria


    Quote from Luna

    ..For instance, if John B. is entered, to immediately tell you his total? As opposed to having ..


    In a copy of the spreadsheet (to prevent altering the original during testing), follow the suggestions above, and try it (since it doesn't appear that you have done this).
    p45cal

    Re: Running Total Based On Specific Criteria


    Quote from Luna

    Thank you for clarifying. Is there any way to base the formula on the new value being entered? For instance, if John B. is entered, to immediately tell you his total?


    That's pretty much what the formula does. Assuming you'd already copied the formula down to row 100 then when you enter a new record at row 80 you'll automatically get the running total for that student on that row, try it and see :smile:

    Re: Running Total Based On Specific Criteria


    Quote from p45cal

    In a copy of the spreadsheet (to prevent altering the original during testing), follow the suggestions above, and try it (since it doesn't appear that you have done this).
    p45cal


    I did try the suggested formula, and the result (a running total for one student) was the reason I asked a follow up question. Of course, I may have entered it wrong and will try again, but I didn't just disregard the suggestion.

    Re: Running Total Based On Criteria


    Here's an example of my suggestion. Note that formula is already copied down to row 20 so you can add details from row 11 and the running total will update automatically.


    If you don't want to show a zero when there is no entry in columns A to C you could change the formula to something like


    =IF(A2="","",SUMIF(A$2:A2,A2,C$2:C2))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login