Summarize Multiple Sheets onto Another Sheet with Two Criteria

I have six sheets, "Log X," with an identical setup and identical column headers. I want to be able to summarize one column, “Amount,” based on criteria from another column, “Budget ID,” on each of the six sheets onto another sheet, "Budget Sheet”.

Here are sample info from two of the six sheets, titled “Log 1” and “Log 2” and the “Budget Sheet”:

I've looked into using SUMIF/SUMIFS and referencing the relevant sheets, but it seem that I cannot reference more than one sheet, so that's not going to work.

Any ideas on how to accomplish this kind of summary reporting?

Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @ariestauro,

    You can reference more than sheet by adding together multiple "SUMIF" formulas. Here is what it would look like for 2 log sheets.

    =SUMIF({Log 1 Budget ID}, [Budget ID]@row, {Log 1 Amount}) + SUMIF({Log 2 Budget ID}, [Budget ID]@row, {Log 2 Amount})

    Just keep adding "SUMIF" sections with new sheet references as needed.

    I'm sure Professor X would accept this answer.

    Hope this helps,

    Dave

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @ariestauro,

    You can reference more than sheet by adding together multiple "SUMIF" formulas. Here is what it would look like for 2 log sheets.

    =SUMIF({Log 1 Budget ID}, [Budget ID]@row, {Log 1 Amount}) + SUMIF({Log 2 Budget ID}, [Budget ID]@row, {Log 2 Amount})

    Just keep adding "SUMIF" sections with new sheet references as needed.

    I'm sure Professor X would accept this answer.

    Hope this helps,

    Dave

  • Thank you, @DKazatsky2 !

    That formula worked.

    I had written the formula with the criteria in the wrong order:

    =SUMIF({Log 1 Amount}, {Log 1 Budget ID}, [Budget ID]@row, ) + SUMIF({Log 2 Amount}, {Log 2 Budget ID}, [Budget ID]@row, )

    Thank you again for your help!