SUMIF Formula needs to be copy to column and row

[adaliu7]

I need to compile data from the same workbook but different sheet with SUMIF formula

The three three SUMIF formulas are all correct, however I don't know how to implant the same data for different column and row:

formula 1:
=SUMIF(January!C4:C10,B11,January!D4:D10)+SUMIF(January!G4:G10,B11,January!H4:H10)+SUMIF(January!K4:K10,B11,January!L4:L10)

Change/copy to next column, criteria NO change but range and sum-range for Month change to February, March, .... November and December.

Change/copy to next row, criteria Change to B12, B13....B19, B20. Range and sum_range do NOT change.



Formula 2:
=SUMIF(January!C14:C16,B23,January!D14:D16)+SUMIF(January!G14:G16,B23,January!H14:H16)+SUMIF(January!K14:K16,B23,January!L14:L16)


Formula 3:
=SUMIF(January!C20:C29,B30,January!D20:D29)+SUMIF(January!G20:G29,B30,January!H20:H29)+SUMIF(January!K20:K29,B30,January!L20:L29)


Formula 2&3 are basically the same just compelling different data. Again, the same how to:
Change/copy to next column, criteria NO change but range and sum-range for Month change to February, March, .... November and December.

Change/copy to next row, criteria Change to B24, B25....B27, B28. Range and sum_range do NOT change.


See attached file, all pink are the one needs to be filled in. Many thanks!SUMIF Problem.xlsSUMIF Problem.xls

[npamcpp]

A formula does note adjust the sheet names when copied across.

You can use Indirect() to reference the sheet name if it is stored somewhere in the spreadsheet, and then combine that with Row() to let the row numbers update when copied down. See attached file for an example with a simple sum formula.

[adaliu7]

A formula does note adjust the sheet names when copied across.

You can use Indirect() to reference the sheet name if it is stored somewhere in the spreadsheet, and then combine that with Row() to let the row numbers update when copied down. See attached file for an example with a simple sum formula.

But my problem is, the data that is input for Sheet January-December are if certain data are entered, then it will appear in categories of summary: SUMIF(January!$C4:$C10,$B11,January!$D4:$D10)

If to use indirect, then data has to be in fixed cell. Any other suggestions? Many thanks!

[Cutter]

Welcome to the forum, adaliu7. Please read the following:

Rule 12: Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

[npamcpp]

You can use Indirect(), but you really need to take care when constructing the formula.

Your data layout does not easily support summarizing. Change your data layout to a simpler format and you can use simpler formulas.