Microsoft Excel for PC Logo
Posted on May 11, 2008
Answered

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!

Answer This Question
  • 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?

×

1 Answer

Kurt Van Etten

Kurt Van Etten

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 35 Answers
  • Posted on May 12, 2008
Kurt Van Etten
Contributor
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.

Joined: Sep 20, 2007
Answers
35
Questions
14
Helped
54286
Points
99

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.

Add Your Answer

×

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1helpful
2answers

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.
Read full answer
Jan 01, 2010 • Microsoft Excel 2007 Home and Student...
0helpful
10answers

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
b6298df.gif 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
Read full answer
Apr 02, 2008 • Computers & Internet
2helpful
2answers

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.)
Read full answer
Sep 18, 2009 • Microsoft Excel for PC
1helpful
3answers

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.
Read full answer
Jul 08, 2009 • Microsoft Office Excel 2003 for PC
1helpful
1answer

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.
Read full answer
Apr 11, 2009 • Microsoft Works 8.0 for PC
0helpful
2answers

Dear Sir in column A i have the following Column A Column B (ResultColumn C ) 1 5 5 -2 7 -7 3 -9 9 -4 -10 -10 5 -2 2 in Column C i need to get only the sign changed i.e. ''-'' or ''+'' accourdingly as...
Put the below formula in column C1 and then drag down for other values. Please rate me if this helps. please.
=(A1* -1)
Read full answer
Mar 11, 2009 • Microsoft Office Excel 2003 for PC
0helpful
2answers

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)
Read full answer
Feb 16, 2009 • Microsoft Excel for PC
0helpful
3answers

Formula
sumif
Read full answer
Jan 20, 2008 • Microsoft Excel for PC
0helpful
5answers

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
Read full answer
Dec 04, 2007 • Microsoft Excel for PC
0helpful
1answer

EXCEL FORMULA
try using the formula "sumif". It allows you to sum by category/name. Good luck! D.
Read full answer
Sep 12, 2007 • Microsoft Office Standard for PC
Not finding what you are looking for?

View Most Popular

Excel for PC

  • Computers & Internet

Related Question

I am trying to figure out a payroll formula. I

  • Microsoft Excel 2007 Home and Student Edition for PC

Related Topics

212 views

Ask a Question

Usually answered in minutes!

Popular Questions

CSV file to Import correctly into Excel 2003

Microsoft Excel for PC

Indian Rupee format in Excel spreadsheet

Microsoft Excel for PC

How to select all sales entry in tally 9 and how to delete it

Microsoft Excel for PC

Excel file opens in read only mode

Microsoft Excel for PC

Using excel to solve problem by trial and error method

Microsoft EXCEL 2004 for Mac

Check Out the Mobile Browsers Report
Check Out the Mobile Browsers Report

View all Fixya Reports

Top Microsoft Computers & Internet Experts

Grand Canyon Tech
Grand Canyon Tech

Level 3 Expert

3867 Answers

k24674

Level 3 Expert

8093 Answers

Brad Brown

Level 3 Expert

19187 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...