Solved

# excel formula need to add a NOT equal value

Posted on 2015-02-07
54 Views
I have the following formula that works fine:
=SUMIFS(Report!H:H,Report!L:L,"Civilian",Report!N:N,"04*")+SUMIFS(Report!H:H,Report!L:L,"Civilian",Report!N:N,"05*")

But a new wrinkle popped in, I can't add values that have the word Commit in column D.  How would that impact the formula above?

Thanks
0
Question by:Matt Pinkston
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 22

Accepted Solution

Ejgil Hedegaard earned 250 total points
ID: 40595714
Use the criteria "<>*Commit*" like this
``````=SUMIFS(Report!H:H,Report!L:L,"Civilian",Report!N:N,"04*",Report!D:D,"<>*Commit*")+SUMIFS(Report!H:H,Report!L:L,"Civilian",Report!N:N,"05*",Report!D:D,"<>*Commit*")
``````
0

LVL 13

Expert Comment

ID: 40595729
You could use a formula like the following to return the number or 0 if it's not a number:
``````if(isnumber(yourcell),yourcell,0)
``````
0

LVL 29

Assisted Solution

IrogSinta earned 250 total points
ID: 40595872
You can combine both SumIfs into one this way:
``````=Sum(SUMIFS(Report!H:H,Report!L:L,"Civilian",Report!N:N,{"04*","05*"},Report!D:D,"<>*Commit*"))
``````

Ron
0

Author Closing Comment

ID: 40598071
Excellent, thanks go me the data I needed
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original souâ€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month3 days, 11 hours left to enroll