SUMIFS formula modification

  • I have a rather large workbook and have been using a SUMIFFS formula that is starting to become a little cumbersome and i need to modify to alleviate constant corrections.

    My example I posted has 3 sheets.

    sheet "TEST1" has a pivot table of data pulled from my server. The problem is some lines will be removed over time.

    Sheet "TEST2" and "TEST3" Has data pulled from TEST1


    The problem i am having is I have written the formula to look in a specific row and Cell so when a row is deleted I have to modify to formula to look in the correct cell. The funny part is probably has worked for several years until recently but i do realize there is a better way just not found it yet.

    my formula is below:

    =SUMIFS(Test1!C8,Test1!A8,$AJ$7,Test1!B8,"G") I tried to keep make sure i referenced the specific item in AJ7.


    I think i should transition this formula to probably a Vlookup but open to any suggestions

  • royUK,


    I do not have a good answer for that. At the time I was working this project I was experimenting with sumifs. This formula worked for many years until input data was rightly changed. when it did i had to fix an onerous amount of formulas to get workbook back up and running. It does ask for the sumifs of one cell which which returns the year. In this case 2021 Column. My desire would be to not have modify code if a row is deleted on test1 sheet. Thanks Billyrj

  • Okay I have been playing with this for a little bit and am struggling with how to resolve.


    On Sheet "Test2" I have aray of data and in cell AK8 my formula "=SUMIFS(Test1!C8,Test1!A8,=SUMIFS(Test1!C8,Test1!A8,$AJ$7,Test1!B8,"G"),Test1!B8,"G")" its was pointed out that i was using a sumifs to return 1 cell. I need to look in "$AJ$7" as a reference to find in column A on sheet "TEST1" find "CAT" then if then match G and copy columns C:I to TEST2.

    I am essentially matching two columns and parsing data. And cannot figure out a better approach with the data i am using. I have tried to use Vlookup and have really struggled.


    Thanks in

  • I have attempted to utilize a Vlookup but still struggling with returning the correct value.


    In K11 I have a formula that is looking in Column "A" for "DOG" and then in Column "B" for "G" then return the value in column "C".


    My formula: =VLOOKUP(K3, IF(B2:B19="G", A2:I19, ""), 3,FALSE )

    I did reference cell K3 as it has 'DOG" but just doesn't seem to work. I attached and updated book. I originally wanted to copy and paste Column C:E but just could not figure that out. Thanks

Participate now!

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