Help with an excel formula

I have the following excel SUMIFS which works well but now I also need to add to the sum column H When Q = Civilian OR column Z contains SOLD

=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"06*")
Matt PinkstonAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
then try
=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"06*")+SUMIFS(Report!H:H,Report!Z:Z,"*Sold*",Report!S:S,"06*")-SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!Z:Z,"*Sold*",Report!S:S,"06*")

Open in new window

0
 
Rgonzo1971Commented:
Hi,
pls try

=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!Z:Z,"Sold")

Open in new window

or
=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"06*",Report!Z:Z,"Sold")

Open in new window

REgards
0
 
Matt PinkstonAuthor Commented:
I made a mistake in the original question it needs to be OR and not AND question was edited
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Rgonzo1971Commented:
then try
=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"06*")+SUMIFS(Report!H:H,,Report!Z:Z,"Sold"Report!S:S,"06*")-SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!Z:Z,"Sold",Report!S:S,"06*")

Open in new window

0
 
Matt PinkstonAuthor Commented:
tried this but did not work
=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"06*"),SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"05*",Report!Z:Z,"Sold")
0
 
Matt PinkstonAuthor Commented:
=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"06*")+SUMIFS(Report!H:H,,Report!Z:Z,"Sold"Report!S:S,"06*")-SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!Z:Z,"Sold",Report!S:S,"06*")

this one got an error pointing to ,"Sold"Report
0
 
Rgonzo1971Commented:
Corrected
=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"06*")+SUMIFS(Report!H:H,Report!Z:Z,"Sold",Report!S:S,"06*")-SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!Z:Z,"Sold",Report!S:S,"06*")

Open in new window

0
 
Matt PinkstonAuthor Commented:
I tested the solution but not sure it works...

the requirements was

contains SOLD isn't the formula set for = SOLD
0
 
Matt PinkstonAuthor Commented:
Awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.