SUMIF Formula Not Working

BlueRhinos

Board Regular
Joined
Aug 31, 2007
Messages
83
I have written a Sumif Formula for the purpose of adding cells within a range that meet multiple criteria. The formula syntax was copied from a MR Excel forum and the parameters were updated in accord with my spreadsheet. The resulting value is 0 as opposed to #NA, which indicates the formula syntax is correct?? I can accomplish my desired result by creating multiple counif formulas and summing them up, but wanted to streamline the process. Why is this formula performing the math? :confused:
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Can you post the formula?

Also some sample data and expected result(s) might be useful.
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Welcome to the forum. Can you hare your formula so we can look at it?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
A value of 0 versus #N/A does not necessarily mean the syntax is correct.

Please provide details about what you are trying to do exactly.
 

BlueRhinos

Board Regular
Joined
Aug 31, 2007
Messages
83

ADVERTISEMENT

Thanks for the welcome! I just discovered I can do what I need to with only a sum formula!! However, I'd still like to learn about SUMIF. Here's a high level summary of what I'm trying to...I have a column of data that contains associates names followed by a specific number of courses taken.
John
4
Jim
3
Dave
2....
I was trying to add up all the numeric data with a sumif (not realizing that excel would ignore the text data when using just a sum formula.).

Here's the formula I was using that produced a 0.

=SUM(IF($E$2:$E$2001=1,IF($E$2:$E$2001=2,1,0),0))
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
That formula will count cells in the range that equal 1.....and equal 2. Of course no cell can equal both 1 and 2 at the same time so the formula will always return 0.

If you actually wanted to count cells that equal 1 or 2 then you could use

=SUM(COUNTIF($E$2:$E$2001,{1,2}))

...but I don't suppose you really want to do that...... :)
 

BlueRhinos

Board Regular
Joined
Aug 31, 2007
Messages
83

ADVERTISEMENT

Thanks for your help Barry. Any idae how to quickly upload an image to replace the flag icon?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
click on "profile" at the top of the page. You should be able to upload an avatar and/or change the flag
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Welcome to our Forums. :)

Note, flags and avatars are two different deals, the Avatar Control Pannel is located at the very bottom of your profile settings. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top