Negative entries

The UK's most regular instructor-led training courses.
Training information: microsoft excel courses tate · Excel training london · Microsoft Excel Training
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version 2007

Negative entries

Konstantin has attended:
Excel Introduction course

by - delegate Konstantin [1 post] (2008 Apr 2 Wed, 16:37) Reply

How does one make Excel sum up only positive values and automatically ignore negative entries in a simple summation?

Version	Date	Location	Places available	Book	Next place rate:
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2009 Jan 14 Wed	Bloomsbury	5	book now	£235	£245
2002/XP	2009 Jan 14 Wed	Tooting	0	FULL
2003	2009 Jan 16 Fri	Southwark	0	FULL
2007	2009 Jan 21 Wed	Bloomsbury	3	book now	£240	£245
2003	2009 Jan 21 Wed	Southwark	2	book now	£235	£240
2003	2009 Jan 23 Fri	Bayswater	4	book now	£195	£205
Full Schedule: See all 113 Excel Advanced course dates. Bookings currently available until 31st March 2010.

RE: Negative entries

by - trainer Rajeev gold contributer [480 posts] (2008 Apr 3 Thu, 23:40) Reply

Dear Konstantin

Thank you for attending Excel Introduction Course. I hope you enjoyed the course and that you benefited from it.

It is possible to calculate only the positive figures. We cover a function called SUMIF in Advanced level that can resolve this query.

The syntax of a SUMIF is :

=SUMIF(Range,Criteria,Sum_range)

I'll provide you with a simple example as follows:

A B C

1 10

2 -20

3 -5

4 5

5 =Sumif(A1:A4,">0",A1:A4)

This should give you a result of 15 on cell A5 because it will not count any negative numbers.

NB: If you are using the Function dialog box then you do not need to put >0 in Quotation marks (" ").

I hope this has helped you in resolving your query.

I would request you to mark the post as resolved!!

Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000/2003

· From the Classroom to the Corporate World
· Database Tips from an Excel Training Course
· VBA Excel 2007
· How to Create Better Excel Spreadsheets: Part Three
· How Small Businesses Profit from Outside Excel Training

Please browse our web site to find out more about
microsoft excel courses tate and other Microsoft training courses.

Excel tip:

Shortcut for deleting all comments in a spreadsheet

If you have entered multiple comments into a spreadsheet and wish to delete them all at once, you can achieve this by:

1. Holding down Ctrl, then Shift, then O - this will select all cells containing comments in the worksheet you are looking at.

2. Right-clicking on one of the selected cells, and selecting Delete Comment from the menu that appears.

3. Clicking anywhere else in the spreadsheet to deselect comments - all comments should have disappeared from the spreadsheet.

View all Excel hints and tips

Rate this page: