Using the sumifs function for multiple conditions across three sheets

  • Good Afternoon Ozgrid,


    I would like total the number of boxes sent to different states for a given quarter. I am running into trouble when I try to group the sites by state. I'm trying to avoid having to create another list grouping the states and sites together. I am hoping I can use functions to be able to group them together instead. I created a diagram on Sheet3 with the commons links between the three sheets. I would be grateful for any thoughts or guidance.


    Thanks,
    Kyle

  • Re: Using the sumifs function for multiple conditions across three sheets


    IN SHEET1 in D2 copy this formula
    =VLOOKUP(A2,Sheet2!$A$1:$B$100,2,0)
    ciot D2 down


    in sheet 3 in C2 copy paste this formula
    =SUMPRODUCT((Sheet1!$D$2:$D$100=TRIM(Sheet3!A2))*(Sheet1!$B$2:$B$100=Sheet3!B2)*(Sheet1!$C$2:$C$100))
    you get 9


    copy C2 down


    I am returning the file now called "klmickalowski,xlsx" see sheet1 and sheet3

  • Re: Using the sumifs function for multiple conditions across three sheets


    Hey venkat1926,


    Thanks for your help! I originally had it that way, but I was trying to avoid adding a column with so much repeating data to sheet1. The file I supplied is a simplified version of what I'm actually working with. I have 50+ sites with 10 quarters of data each and roughly 20 columns of variables in Sheet1. Sheet2 lists each site and has other columns of information that does not change from quarter to quarter (address, phone number, etc.). I was hoping there was a way to match the sites on sheet2 to the state desired in sheet3 without having a column in sheet1 with so much repeating data. Do you have any idea if there is a way to do that with named ranges, or arrays? Thanks again for your help.

  • Re: Using the sumifs function for multiple conditions across three sheets


    try this macro and see sheet3 C2.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!