marknovom
Level 1 (4 points)
iWork

Q: sum of only positive numbers

I'm the dean at a high school and I need to keep track of student demerits and detentions served.  I've created a spreadsheet pictured below.  As you can see every time a student receives a demerit, I put it all the info in one row (date, teacher giving the demerit, what the demerit was for, and how many demerits received for the infraction).  So you can imagine at the end of the quarter, this spreadsheet is quite large (400-500 rows). I also put a negative number (-2) when the student serves the detention (a student needs to serve every 2 demerits received. 

 

I wrote about this formula on this forum almost two years ago.  I have another tab (sheet) that adds up all the demerits, so I can go to that tab (sheet) and see who needs to serve.  The formula was =Sumif(Sheet1!A:A,A:A,Sheet1H:H) and it works wonderfully. This other sheet has our entire student body on it (each student only listed once...so if we have a student population of 300 students, there are only 300 rows). It adds up all the points (even the negatives), so I can see who has more than 2 demerits and who needs to serve.  In the example below Steve Abrahim would have 0 demerits on the second sheet because he received 4, but also served 4.

 

But I now realize that I need a second tab (sheet) that has the total number of demerits a student receives in the quarter.  So, I would need a formula that only adds up positive numbers.  So, again, that sheet will have our entire student body listed in Column A (one student per row).  So on this tab (sheet), Steve Abrahim would have 4 demerits (even though he served two detentions) and Adam Butler would also have four (even though he only served one detention).  Can someone write this formula?

 

Please let me know if you need any clarification on my question.

 

Thank you,

 

Mark

 

 

 

 

Screen Shot 2016-08-19 at 6.21.23 AM.png

iMac, OS X Mountain Lion

Posted on Aug 19, 2016 3:35 AM

Close
marknovom

Q: sum of only positive numbers

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Aug 19, 2016 5:27 AM in response to marknovom
    Level 6 (19,421 points)
    iWork
    Aug 19, 2016 5:27 AM in response to marknovom

    =sumif(E, ">=0")

     

    where I think the Points column is column E

  • by marknovom,

    marknovom marknovom Aug 22, 2016 11:02 AM in response to Wayne Contello
    Level 1 (4 points)
    iWork
    Aug 22, 2016 11:02 AM in response to Wayne Contello

    Thank you for this.  But the formula needs something else.  I guess I wasn't clear.  It needs to reference the students' names, so that it only adds up positives for Steve Abrahim.  So on the tab (sheet), each student is only listed once.  So when I go to Steve Abrahim's row, I'll be able to see how many points he had for the quarter.  When I look at Adam Butler's name, I'll be able to see how many points he had for the quarter.  And it only adds up the positive numbers, so I'll see how many demerits/points each student had for the quarter.

     

    Thanks,

     

    Mark

  • by Wayne Contello,

    Wayne Contello Wayne Contello Aug 22, 2016 12:11 PM in response to marknovom
    Level 6 (19,421 points)
    iWork
    Aug 22, 2016 12:11 PM in response to marknovom

    In the table "Demerit Summary" (on the right) the formula should change to:

    B2=SUMIFS(Demerit Data::E, Demerit Data::E, ">=0", Demerit Data::A, A2)

     

     

    like this:

    Screen Shot 2016-08-22 at 2.10.33 PM.png

  • by marknovom,

    marknovom marknovom Aug 24, 2016 3:22 AM in response to Wayne Contello
    Level 1 (4 points)
    iWork
    Aug 24, 2016 3:22 AM in response to Wayne Contello

    It's still not working.  I have the Demerit Data on a different sheet...not a different table (meaning, you have to click on the tabs above to look at them).  Is that the reason why it's not working?

  • by Wayne Contello,Helpful

    Wayne Contello Wayne Contello Aug 25, 2016 3:38 AM in response to marknovom
    Level 6 (19,421 points)
    iWork
    Aug 25, 2016 3:38 AM in response to marknovom

    yup.

     

    the canonical form of a reference is:

    <SHEET NAME> :: <TABLE NAME> :: <CELL RANGE>

     

    In the formula I provided:

    B2=SUMIFS(Demerit Data::E, Demerit Data::E, ">=0", Demerit Data::A, A2)

     

    <SHEET NAME> does not need to be used because the tables are on the same sheet. you should add this

    <TABLE NAME> should be changed to match the actual name of your table

  • by SGIII,

    SGIII SGIII Aug 24, 2016 6:56 AM in response to marknovom
    Level 6 (10,796 points)
    Mac OS X
    Aug 24, 2016 6:56 AM in response to marknovom

    marknovom wrote:

     

    It's still not working.  I have the Demerit Data on a different sheet...not a different table (meaning, you have to click on the tabs above to look at them).

     

    To make it easier to set up the formulas, rather than worrying about typing a full reference, why not move the summary table to the same sheet as Demerit Data, set up the formula, and then move the summary table back to its separate sheet.

     

    To move the table just click in it, then click the "bulls-eye" to its upper left so the entire table is selected, command-x to cut, go to the sheet with the Demerit Data table, command-v to paste, and the table will appear on that sheet.  Then set up the formulas by selecting the ranges, rather than typing in formulas.  Once the formulas are working you can move the table back to where you want it via the same cut-paste sequence.

     

    SG

  • by marknovom,

    marknovom marknovom Aug 25, 2016 3:58 AM in response to Wayne Contello
    Level 1 (4 points)
    iWork
    Aug 25, 2016 3:58 AM in response to Wayne Contello

    It's still not working for some reason.  Let me attach another screen shot.  As you can see, the tab is called "Sheet 1".  Then I have another tab called "Totals".  The demerits are in column H (we call them DSH).  Column I are classroom demerits (called ACT....for instance a student not doing homework). 

     

    The positive numbers are when they receive a demerit.  The negative numbers are when they serve "detention" and bring their demerit number to zero.

     

    Screen Shot 2016-08-25 at 6.44.16 AM.png

     

    In the Totals tab (see below), I list our entire student body (so, there are actually a lot more columns than picture below)...with each student listed only once.  I want the total number of positive numbers of Steve Abrahim's DSH demerits to go in Steve Abrahim's row (row 2) in the "Totals" tab in column E (TOTAL DSH).  I also want the total number of positive numbers of Steve Abrahim 's ACT demerits to go in Steve Abrahim's row (row 2) in the "Totals" tabe in column F (TOTAL ACT).

     

    Screen Shot 2016-08-25 at 6.51.45 AM.png

    Thank you for your help!

     

    Mark

  • by SGIII,

    SGIII SGIII Aug 25, 2016 4:53 AM in response to marknovom
    Level 6 (10,796 points)
    Mac OS X
    Aug 25, 2016 4:53 AM in response to marknovom

    Can you post a screenshot showing your actual formula?  You can do that by selecting the cell with the formula and including the lower left corner when you take the screenshot.

     

    Also, have you considered, as suggested above, setting up the formulas while both tables are on the same sheet (tab) and then moving (cutting-pasting) the summary table to another sheet after everything is working?

     

    SG