Sumif for multiple criteria, criteria ranges and sum ranges

Hey guys,

I'm trying to do a sumif or sumifs but for multiple criteria ranges and sum ranges based on different criteria. Here is the example so that hopefully it is easier to understand.

IF column B:B = w or x, sumif(C:C,A1,D:D), IF column B:B = y or z, sumif(E:E,A1,F:F)

Thanks for your help!

Thanks for the reply vlday. So how would i combine those two equations? i don't actually want to add the two results together, just want have it be all one formula and return one answer. hopefully i can clarify better - those two IF statements I wrote, I'd like them to be combined for ultimately one answer return. So if column B is w or x, do the sumif that searches the criteria range of column C and sums column D. If Column B is y or z, do the sumif that searches the criteria range of column E and sums F. Hopefully that clarifies my intentions. Thanks!

I have attached a sample data set. There are 3 tabs. The "summary" tab column C is the one to focus on. The numbers I want are already in Column C of the summary tab- except for right now I have two different formulas in Column C to achieve my goal. The cells in yellow have one sumif and the white cells have another sumif. I want to be able to drag down one single formula in the whole Column C and achieve the same numbers. I basically need to combine the 2 formulas in column C so that i don't have to filter to drag down separate formulas depending on what gets added to the next row.

NOTE: I must keep tabs "1" and tab "2" separate and in separate columns - can't just add tab 2's columns to bottom of tab 1's data. (I deleted out a lot of other data)



Thanks!

Forgot to add that what is in Column B (w,x,y or z) of the summary tab is determining which sumif to use in Column C.

Formula:

Please Login or Register  to view this content.

paste that into c2 it should work but i would suggest limiting your data range from A:A,B:B to the maximum rows you will be using.

HI,

check the updated sheet if this is what you are trying to achieve i shall explain you


Punnam

Hi,

If you are interested to learn more about this you can click on below link of the Excel tip's tutorial..........

http://www.exceltip.com/summing/summ...-criteria.html

Thanks
Nisha

Thanks for all the replies! I ultimately took daveisalwayshere's formula but added one more sumif to include variable 'Z'.

Punnam, yours was almost perfect - interestingly enough, it added in an additional amount of 200 in line 21 that should not have been included. Not understanding the formula, I'm not sure why it picked up an amount for that row. Maybe you can shed some light or adjust the formula?

Thanks again to everyone who replied.

Sorry, Punnam, , I misspoke. Your formula does capture everything, there was a slight nuance with the variable for that line 21 in my true data and yours actually picks it up, where I had to make an adjustment before. Can you explain a little more about the formula? Thanks