Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 58
  • Last Modified:

excel formula need to add a NOT equal value

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
Matt Pinkston
Asked:
Matt Pinkston
2 Solutions
 
Ejgil HedegaardCommented:
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*")

Open in new window

0
 
Mark BullockQA Engineer IIICommented:
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)

Open in new window

0
 
IrogSintaCommented:
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*"))

Open in new window


Ron
0
 
Matt PinkstonAuthor Commented:
Excellent, thanks go me the data I needed
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now