sum of only positive numbers

Question

Level 1

24 points

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

iMac, OS X Mountain Lion

Posted on Aug 19, 2016 3:35 AM

Reply

Answer 1

Aug 19, 2016 5:27 AM in response to marknovom

=sumif(E, ">=0")

where I think the Points column is column E

Reply

Answer 2

marknovom Author

Level 1

24 points

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

Reply

Answer 3

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:

Reply

Answer 4

marknovom Author

Level 1

24 points

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?

Reply

Answer 5

Aug 25, 2016 3:38 AM in response to marknovom

yup.

the canonical form of a reference is:

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

Reply

Answer 6

SGIII

Level 8

37,568 points

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

Reply

Answer 7

marknovom Author

Level 1

24 points

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.

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).

Thank you for your help!

Mark

Reply

Answer 8

SGIII

Level 8

37,568 points

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

Reply