SUMIFS w/ multiple criteria and an or function

Data Example.xlsx

Hey All,

I'm trying to do a sum function for two criteria basically.

One being the project number (see excel) and the other being a few symbols in that project which compose an expense category.

For example I'd being looking for the total of all personnel costs (900 codes on spreadsheet) which are under project A. I'm hoping there is an easy way to do it where I can do an or and not like a sum(sumifs) for each of the project and symbol codes. something like sumifs(amount, project code, =A, symbol, =or(900,901,902) etc...

Thanks folks,

-Jake

+1 on the pivot table...


But for a formula, Try

=SUMPRODUCT(SUMIFS('Data Project Costs'!$C$2:$C$100,'Data Project Costs'!$A$2:$A$100,"A",'Data Project Costs'!$B$2:$B$100,Summary!$B$1:$B$8))

Awesome, for the pivot table is there a way to create custom sums i.e. those 7 codes are Personnel and I ultimately just need one number for that and there are also other cost categories that contain upwards of 10-15 symbols which I need one number for.

I realize that I can likely just add a column with the associated cost category, however it'd be nice as new data comes in if I could enter that into the data sheet and not manually enter the cost category. Hopefully that makes sense.

With that formula it seems to work however I get a difference in the values. Is that from rounding related to the sumproduct? If so, howcan I correct that?

Thanks Again

Originally Posted by jacobkmc

With that formula it seems to work however I get a difference in the values. Is that from rounding related to the sumproduct? If so, howcan I correct that?

Can you be more specific?
What value did it actually return?
What value did you expect and why?

Can you post another book showing this behavior?

Thanks, you know what I tried using the formula for a different data set and realized I was missing a symbol code so it worked perfectly!

Quick question though, why do you need sumproduct, it seems like logically it should work with just the sumifs?

Because SUMIFS does AND criteria, not OR
So putting it in SUMPRODUCT makes it create 1 SUMIFS for each of the OR criteria, then sums them.