Office & Productivity Software forum

General discussion

Microsoft Excel 2007Sum if >0

by nhearn / June 15, 2009 6:27 AM PDT

I need to have a formula that sums specified (non-consecutive) cells if the cells have a total of 0 or more. (i.e. I don't want any negative numbers counted.) If the cells are, for instance:

12 | -135 | 24 | 0 | 47

I need the formula to sum only the 12, the 24, and the 47, without factoring in the 0 or the -135.

Can you provide any help on this, especially since the cells are not next to eachother? I'd appreciate it.

Thank you!

Discussion is locked
You are posting a reply to: Microsoft Excel 2007Sum if >0
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Microsoft Excel 2007Sum if >0
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
If the cells are in
by MarkFlax Forum moderator / June 15, 2009 6:41 AM PDT

row 1, eg A1, B1, C1, D1, E1, F1, etc

then formula could be something like;

=0+IF(A1>0,A1,0)+IF(B1>0,B1,0)+IF(C1>0,C1,0)+IF(D1>0,D1,0)+IF(E1>0,E1,0)

The first 0 just sets the addition off. If the cell value is not greater than 0, then just 0 is added.

It's not elegant, but it will get you what you want.

Mark

Collapse -
Continuing saga....
by nhearn / June 15, 2009 7:07 AM PDT
In reply to: If the cells are in

Thank you so much for your help; I used this formula, as you suggested:

=0+IF(E3>0,E3,0)+IF(I3>0,I3,0)+IF(M3>0,M3,0)+IF(Q3>0,Q3,0)+IF(U3>0,U3,0)+IF(Y3>0,Y3,0)+IF(AC3>0,AC3,0)+IF(AG3>0,AG3,0)+IF(AK3>0,AK3,0)+IF(AO3>0,AO3,0)+IF(AS3>0,AS3,0)

However, it is returning the total of the negative numbers as well (i.e. the final product is something like -14000.)

Any ideas why it would be doing this?

Again, thank you!!

Collapse -
That's strange.
by MarkFlax Forum moderator / June 15, 2009 8:21 PM PDT

Sorry for the delay, (time difference for me here in the UK).

That's strange. I don't use Excel 2007 myself as I am still on Excel 2002, (XP), but the formula should still work because it is just a simple IF statement. Without your actual figures, I can't check your spreadsheet's math, so I did some more testing myself.

Since your target cells are spread out and are not consecutive, I tested a couple of ways. I used the same cells as you have quoted, but with my own figures, eg;
E3= 10, I3= -50, M3= 23, Q3= 0, U3= -420, Y3= -510, AC3= 740, AG3= 5, AK3= 44, AO3= -2, AS3= 410

Then I copied these to B12 to B22 to create a simple, one column, list, eg;
B12= E3
B13= I3
B14= M3
B15= Q3
B16= U3
B17= Y3
B18= AC3
B19= AG3
B20= AK3
B21= AO3
B22= AS3

Doing a SUM function on (B12:B22) gives 250, so that is my test figure. It adds them all, including zeros and minus figures.

Using the formula I gave on the Row 3 figures, eg;
=0+IF(E3>0,E3,0)+IF(I3>0,I3,0)+IF(M3>0,M3,0)+IF(Q3>0,Q3,0)+IF(U3>0,U3,0)+IF(Y3>0,Y3,0)+IF(AC3>0,AC3,0)
+IF(AG3>0,AG3,0)+IF(AK3>0,AK3,0)+IF(AO3>0,AO3,0)+IF(AS3>0,AS3,0)

That gives me 1232. That means it just adds up the positive numbers.

As a double check, using a similar formula on the Col B figures, eg;
=0+IF(B12>0,B12,0)+IF(B13>0,B13,0)+IF(B14>0,B14,0)+IF(B15>0,B15,0)+IF(B16>0,B16,0)+IF(B17>0,B17,0)
+IF(B18>0,B18,0)+IF(B19>0,B19,0)+IF(B20>0,B20,0)+IF(B21>0,B21,0)+IF(B22>0,B22,0)

That gives me 1232, the same result.

So, I am mystified. Can I ask, where do your figures in the Row 3 cells derive from? If there is anything unusual about those cell contents, that might account for the discrepancy. But I can't see what would cause that.

Can you do an error check audit? Excel 2007 has a similar function to Excel 2002 called "Formula Auditing > Evaluate Formula", which can step you through what Excel evaluates each part of the formula to.

That Formula Auditing isn't in the same place in E2007 that it is in E2002, so have a look at the guidance in the link below, to get you there;
http://www.dummies.com/how-to/content/understanding-excel-2007s-formula-auditing-tools.html

It's called "Dummies.com". Don't worry about that name though, I'm sure it's not meant to be personal,

Can you step through your formula, and see if you can find out where E2007 is making any errors? In truth, Excel doesn't normally make errors and we generally find that there's an error or unexpected consequence of a cell we completed.

Let me know if you find anything.

Mark

Collapse -
by nhearn / June 16, 2009 5:24 AM PDT

Hi, Mark;

Thank you so much for investing so much time in researching my current problem! I am really grateful; it?s been helpful.

Here?s some more information; the spreadsheet is for determining mileage on vehicles throughout the year (I help to manage a fleet). The format goes something like:

A1: Vehicle number B2: Vehicle Starting Mileage for January C2: Vehicle Ending Mileage for February D2: Total Mileage for February using IMSUB(C2,B2).

My formula repeats itself using all twelve months, then has figures at the end for totals (total yearly mileage, average weekly mileage, etc).

The problem is that I am pulling the mileage information from driving reports that people submit to me; if, for instance, the reports are not submitted during January, the beginning and ending mileage will be ?0?, and if a half-month is missing, it will be even more difficult (i.e. the ending mileage for November might be 500 miles different from the next report I have, from mid-December).

So I added an additional column to each month (it would be E2 in the above representation) with the formula: ?=IF(C2:D2>0,IMSUB(G2,D2),0)? That way I get the difference between the beginning and ending mileage of the two months.

The formula I originally asked for help about is the one totaling all of the additional columns I added (E2 and similar). So yes, it is based on a formula, and maybe the formula I have for those cells is messed up. Maybe that is throwing off the calculations, although I don?t think it should.

The reason there are negative numbers to begin with is because one month will have ?0? mileage records, then the next month will have the normal mileage reading (i.e. 14,275) and when the IMSUB goes to formulate things, it takes the 14,275 away from the 0, resulting in a negative number.

I don?t know if it would be easier to email you a Word ?02 compatible version of the whole form and have you look at it from there, because, frankly, my explanations above are a little bit hard to understand. Sorry about that.

Is the problem in the previous formula (?=IF(C2:D2>0,IMSUB(G2,D2),0)?)? I am not very good at formulas in Excel, so it wouldn?t surprise me the least bit if that formula is somehow not working.

Anyway, thank you again, so much!

I did try the Excel 2007 auditing and error checking steps, and either I didn't understand what it was saying, or else there is nothing wrong.

Also, if you need to contact me to get a copy of the spreadsheet itself, my email address is nhearn AT cityofhesperia.us (replace spaces and 'AT' with '@').

Thank you!

Nicole

Message was edited by: admin to edit email address to prevent spam harvester from picking it up.

Collapse -
Ahh, that IMSUB function.
by MarkFlax Forum moderator / June 16, 2009 6:15 AM PDT

I wonder if that is the right function to use here. I am aware of it but have never used it myself, as it is an Engineering Function for handling complex numbers. Is that appropriate in your case?

Here's another discussion about IMSUB in these forums last year;
IMSUB.

I think I can see where this problem lies though. Disregarding IMSUB, you need the total mileage done for each fleet car, for each month. That's a simple C2-B2 calculation, where C2 is the mileage at the end of the month, and B2 is the mileage at the beginning of the month. The 'end' of the month figure is not necessarily the 'beginning' of the next month's figure, especially if the drivers do not submit their figures in time.

This means that each 'beginning' of the month should have a default figure of 0, unless you change it to input 'actual' figures when you finally get them. The fault of that is not yours, it is the drivers who delay passing you the figures.

With a default figure of zero, you should not get any minus figures. In your example, if one month, (eg January), has 0 mileage, and the next month,(eg February), has 14,275, that 'next month' calculation will be (End figure at end of Feb = 14,275 minus Start figure at beginning of February = 0), or 14,275 - 0 = 14,275

So looking at your A row again, you need additional columns, eg;

A1 Vehicle #?
B1 Starting mileage at 1/1/09 (default=0)
C1 End mileage at 1/31/09
D1 Total mileage for vehicle #? for January = C1 - B1
E1 Start mileage at 2/1/09 (default=0)
F1 End mileage at 2/28/09
G1 Total mileage for vehicle #? for February = F1 - E1
{repeat as necessary}.

Of course, these are all in the same row, but these forums do not tabulate well.

Does that help? If not let me know.

Mark

Collapse -
In mysterious cases like this ...
by Kees Bakker / June 16, 2009 8:19 AM PDT

it helps to break it down in steps.

Below the numbers make a new row, with formulas like
= max (a1,0) in b1
= max (a2,0) in b2
etc
Max is a somewhat simpler way of getting only positive numbers. Negative numbers will become 0.

Then sum this new calculated cells to another cell (=sum(b1,b2,etc))

Then hide the new row.

Three easy steps that are easy to check also. There might be smarter ways I've never mastered, but this should surely work.

Kees

Collapse -
Followup.....
by nhearn / June 17, 2009 2:33 AM PDT

Thank you so much for your continued help!

Mark, I was able to successfully remove the IMSUB from the beginning and ending calculations, using the C2-B2 calculation you showed below. Thanks!

There is one complication left, with both your advice and Kees??.I think I?m not understanding exactly what you?re suggesting.

The current formula (using C2-B2 calculations) is:
C2: 80873 (January beginning mileage)
D2: 81530 (January ending mileage)
E2:
F2: C2-D2 (Total January Mileage)
G2: 81536 (February beginning mileage)
(etc.)

What I would like to see in cell E2 is the difference between G2 and D2 (in this case, it should be 6 miles). If I did a simple subtraction calculation, that would work great (G2-D2=6), but the complication would arise if, for instance, nobody submitted reports for February; at which case, the G2 column would change:

G2: 0
Which means E2 would change: (0-81530 would equal -81530).

How can I get a formula for E2 that says:

If G2>0
And if D2>0,
Then G2-D2

I can do one or the other (=if(G2>0,G2-D2,0) or =if(D2>0,G2-D2,0)) but I can?t do both, and it really needs to do both, because it is just as often that G2 will be blank, as it is that D2 will be blank.

Which brings me to another concern, that I know there must be some solution to, but it seems to make my head spin thinking about it; if we take the chart to three months instead of the two-month timeline we?ve been summarizing below, the numbers might look like:

C2: 80873 (January beginning mileage)
D2: 81530 (January ending mileage)
E2:
F2: C2-D2 (Total January Mileage)
G2: 0 (February beginning mileage?no reports submitted)
H2:0 (February ending mileage?no reports submitted)
I2:
J2: H2-G2 (Total February Mileage)
K2: 81575 (March beginning mileage)
L2: 82360 (March ending mileage)

As you can see, the numbers would be completely faulty even if the E2 and I2 columns correctly factored in only positive numbers: for instance, since January ends at 81530 and March begins at 81575, there are 45 miles unaccounted for, but both of the E2 and I2 columns would say that there is no mileage unaccounted for.

I?m sure there?s some way to do it, as Excel can do practically anything, but I?m beginning to wonder if I?m in over my head with my current expectations for this form!

Thank you again, both of you, for all you?ve done to help out with this project. I am so grateful!

(Also, thanks for saving me from the wicked and evil trolls, Lee and Mark.)

Collapse -
Yep, those trolls are evil!
by MarkFlax Forum moderator / June 17, 2009 7:20 AM PDT

No problem.

Just a small correction from your post. You calculated cell F2 as C2-D2, but of course it would have to be D2-C2, leaving a distance traveled in January of 657 miles, instead of -657 miles.

Nicole, I''m not sure how much further we should go with this. Your original post was about a problem with minus figures, and I feel we have all, yourself included, worked through that difficulty.

The issues you now have are, it seems to me, related to late notification of mileage traveled figures from your car fleet service. The trouble you are experiencing is that you cannot second guess what those figures are going to be, and so anything you enter into the appropriate cells, (the 'first of the month' and 'last of the month' cells), are going to be incorrect and will cause problems down the line, unless you get the actual and accurate figures. That's a management problem with processes, and not an Excel problem.

But realizing this, that's why I suggested entering default figures of 0, (zero), into the cells where data is to be collected, so that, if data is late, at least the formulas still work. Entering that data later should automatically correct the figures and analysis you need.

One thing I see from your latest post above, is that E2, I2, M2, etc, is not needed. They are blank and serve no purpose because you now have start and end mileage figures for each month, instead of your previous method of using the 'End of month' mileage as the figure for the start of the next month. If 0 is entered in all of the mileage data cells initially by default, then you will get no minus figures.

Correction. If mileage is entered in a particular month's 'Start' cell, but you receive no data for the end mileage for the end of that month, then you will get a minus figure. But again, that is the fault of the reporting system, and not of the spreadsheet, and to avoid that, you re-enter 0 in that Start cell for the time being.

You're right that, if one month's figures are missing, then the next month's figures will not be accurate. But as I said, you can't double guess that, and Excel can't fix it for you.

What you can do, but this depends on your working relationship with your boss/team-leader if you have one, is to show them this professional looking spreadsheet, and show them the raw data, so that they can see where the problem lies with the late reporting of the mileage figures. I am fairly sure that that person will;

1] Be impressed by the spreadsheet, (most bosses I know have never attempted to create one themselves), and

2] Be able to see the 'physical' problems of the system breakdown, the reporting delay.

Perhaps there is something you can do to highlight any questionable figures.

If you are using charts, use bar charts. That way, if any vehicle is not shown as being used in any month, the bar for that month will be missing. In addition, if that month's data is missing but the next month's data is given, then the bar for the next month will be much higher, perhaps off-scale. That's a great visual tool for highlighting problems.

Whilst I have highlighted apparent working practices problems, don't hesitate to come back again if you do have Excel problems.

Mark

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

CNET FORUMS TOP DISCUSSION

Help, my PC with Windows 10 won't shut down properly

Since upgrading to Windows 10 my computer won't shut down properly. I use the menu button shutdown and the screen goes blank, but the system does not fully shut down. The only way to get it to shut down is to hold the physical power button down till it shuts down. Any suggestions?