Question about Microsoft Excel for PC

One in general should not use Find & Replace.

I would recommend always creating the first formula manually by selecting the cells. This will avoid common errors like extra spaces.

Posted on May 12, 2008

Here is how you could do the formula with two one column having the hours of each day in it and the overtime being in another cell:

Example:

Column A Column B

8

8

8

2

8

8

0

Total 40 OverTime 2

Formula in this cell where 40 is at:

=IF(SUM(A1:A7)>40,40,SUM(A1:A7))

Formula in cell where 2 is at:

=IF(A8>40,0,SUM(A1:A7)-A8)

Let me know if this helps.

Example:

Column A Column B

8

8

8

2

8

8

0

Total 40 OverTime 2

Formula in this cell where 40 is at:

=IF(SUM(A1:A7)>40,40,SUM(A1:A7))

Formula in cell where 2 is at:

=IF(A8>40,0,SUM(A1:A7)-A8)

Let me know if this helps.

Jan 01, 2010 | Microsoft Excel 2007 Home and Student...

Yes,

That is Select the Cells which you want to SUM and Press**Alt+=**

This will bring the total just below the selected cells.

Thanks

Iqbal

- Select the column. (click on top of the column it will select)
- Right Click and select Format Cells.
- Select
**Currency**in**Category**then press**OK**

That is Select the Cells which you want to SUM and Press

This will bring the total just below the selected cells.

Thanks

Iqbal

Apr 02, 2008 | Business & Productivity Software

You can number rows in a column by entering a number in cell A1 (usually the number 1 but youcan start with any number) and the formula (=A1+1) in the next row. The result there will be 2. Copy that formula down the rows you want to number and they will be numbered 3, 4, 5, etc. Each row adds 1 to the previous row so if you do anything that disrupts the sequence (like inserting a row between two others) you will have to copy the formulas down again to restore the sequence.
You can also use the Edit-Fill-... menu command to put a series of numbers into rows. Put the starting number in th efirst row. Highlight it and the rows that you want to number and select Edit-Fill-Series... Those numbers will not change if you insert columns or move the formulas.

Or you can use the formula =ROW(A1) in any cell to return the number of that row. (The result of =ROW(A1) is the number 1 in cell A1, the result of =ROW(A2) is the number 2 in cell B2, etc. In this case inserting rows will not affect the numbering (i.e. row A5 will always be numbered 5 even if the data in it is moved down.)

Or you can use the formula =ROW(A1) in any cell to return the number of that row. (The result of =ROW(A1) is the number 1 in cell A1, the result of =ROW(A2) is the number 2 in cell B2, etc. In this case inserting rows will not affect the numbering (i.e. row A5 will always be numbered 5 even if the data in it is moved down.)

Sep 18, 2009 | Microsoft Excel for PC

Subtraction can be done in two ways in Office using the following formulas in the formula (fx) bar:

The SUM formula subtracts by changing the sign of the reference. Remember the sign rules, and thus - -x is in effect +x.

- =A1-A2
- =SUM(A1,-A2)

The SUM formula subtracts by changing the sign of the reference. Remember the sign rules, and thus - -x is in effect +x.

Jul 08, 2009 | Microsoft Office Excel 2003 for PC

Use the following formula as an array formula, you need to hit "Shift+Ctrl+Enter" after entering the formula. Lets say you have the Name in column "B", the Age in column "C" and the amount paid in column "D". Here is the formula.

=SUM((B2:B7="Abhilash")*(C2:C7>0)*(D2:D7))

It will calculate the amount paid if the name =Abhilash" and the Agr is greater than zero.

=SUM((B2:B7="Abhilash")*(C2:C7>0)*(D2:D7))

It will calculate the amount paid if the name =Abhilash" and the Agr is greater than zero.

Apr 11, 2009 | Microsoft Works 8.0 for PC

Put the below formula in column C1 and then drag down for other values. Please rate me if this helps. please.

=(A1* -1)

=(A1* -1)

Mar 11, 2009 | Microsoft Office Excel 2003 for PC

Add another colum, say D with result of B*C

Do a list of the different cat, and use sumif

For cat 0101 (in cell F1)

formula to put in G1

=sumif($A$1:$A$7000;F1;$D$1:$D$7000) where F1 contain 0101

expand the formula for other cat. (G2, G3, etc)

You can use a assistant to extract the distinct cat from A1:A7000, sorting them and copy the result in F (Menu Data-> Filter -> elaborate filter)

Do a list of the different cat, and use sumif

For cat 0101 (in cell F1)

formula to put in G1

=sumif($A$1:$A$7000;F1;$D$1:$D$7000) where F1 contain 0101

expand the formula for other cat. (G2, G3, etc)

You can use a assistant to extract the distinct cat from A1:A7000, sorting them and copy the result in F (Menu Data-> Filter -> elaborate filter)

Feb 16, 2009 | Microsoft Excel for PC

at first select the 1st page data and select data-subtotal from the upper menu list.

then enter the formula as =sum(1stpageSubTotal,2ndpageSubTotal,3rdpageSubTotal) then Press Enter.

If not solved pls get me that data as to reference.

thanks

then enter the formula as =sum(1stpageSubTotal,2ndpageSubTotal,3rdpageSubTotal) then Press Enter.

If not solved pls get me that data as to reference.

thanks

Dec 04, 2007 | Microsoft Excel for PC

try using the formula "sumif". It allows you to sum by category/name.
Good luck!
D.

Sep 12, 2007 | Microsoft Office Standard for PC

Jan 28, 2016 | Microsoft Excel for PC

158 people viewed this question

Usually answered in minutes!

Thank you anyway forum but I have worked it out myself!

The problem is that there was a space after the "Z" in my data set. As soon as I changed my formula to include a space after the "Z" it worked like a charm. So simple and yet it eluded me for months.

=SUMIF($M$4:$M$41,"=Z ",$N$4:$N$41)

Thank you for taking the time to comment. Would you be able to expand on it a little? Do you mean that I should not have typed a "Z" in my formula but selected a cell that contained it?

I tried the following but it came up blank (Cell M41 contains a Z)

=SUMIF($M$4:$M$41,"=M41",$N$4:$N$41)

I use Excel often in my work and I am keen to avoid time wasting errors

What's is sumif?

why are the excel use?

what should be sumif in excel?

×