Question about Microsoft Excel for PC

# SUMIF formula I have data in 2 columns First column has categories such as A1, J1, Z etc. Second column has figures. I would like to sum column 2 conditional on the category (Column 1) it is in. I have used the SUMIF as follows: =SUMIF(\$M\$4:\$M\$41,"=A1",\$N\$4:\$N\$41) So far so good. However when I repeat the same formula for the "Z" category the cell result is blank despite the fact there are figures in the Z category. =SUMIF(\$M\$4:\$M\$41,"=Z",\$N\$4:\$N\$41) To deal with this I usually find & replace the Z with something else but I am sure there is a simple solution that I am missing!

Posted by on

• 1 more comment
• DonnaEm May 12, 2008

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)

• DonnaEm May 12, 2008

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

• Anonymous Mar 02, 2009

What's is sumif?
why are the excel use?
what should be sumif in excel?

×

• Level 1:

An expert who has achieved level 1.

MVP:

An expert that gotÂ 5 achievements.

Governor:

An expert whose answer gotÂ voted for 20 times.

Hot-Shot:

An expert who has answered 20 questions.

• Contributor

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

## 1 Suggested Answer

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
goodluck!

Posted on Jan 02, 2017

×

my-video-file.mp4

Complete. Click "Add" to insert your video.

×

## Related Questions:

### I am trying to figure out a payroll formula. I

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.

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

### In Excel adding positive and negative numbers

Yes,

1. Select the column. (click on top of the column it will select)
2. Right Click and select Format Cells.
3. Select Currency in Category then press OK
Now just type your Numbers i.e. 571 for \$571 and -650 for -\$650 and sum using the normal way.

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

Apr 02, 2008 | Computers & Internet

### How to number a column. I can't remember the short formula, something like a1+1= which would make the number two pop up in the cell underneath.

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

Sep 18, 2009 | Microsoft Excel for PC

### Ms-office

Subtraction can be done in two ways in Office using the following formulas in the formula (fx) bar:
1. =A1-A2
2. =SUM(A1,-A2)
The above formulas refer to cell reference A2 subtracted from A1. As usual, you can use number values instead of cell references.

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

### Sumif function....

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.

Apr 11, 2009 | Microsoft Works 8.0 for PC

### Excel farmulas

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)

Feb 16, 2009 | Microsoft Excel for PC

### Formula required

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

Dec 04, 2007 | Microsoft Excel for PC

### EXCEL FORMULA

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

Sep 12, 2007 | Microsoft Office Standard for PC

## Open Questions:

#### Related Topics:

159 people viewed this question

## Ask a Question

Usually answered in minutes!

Level 3 Expert

Level 3 Expert