Excel - Consolidating Weeks into Months with SUMIF/INDEX/MATCH

0
votes

I'm trying to resolve an issue in Excel that requires consolidating weeks into months is greatly appreciated. (I'm frustrated with myself that I can't figure this out.)

Background

Sheet 1 (Weeks):

  • Shows columns with the date of every Monday for ~52 weeks in 2019 - e.g. R = 1/1/2019, S = 1/7/2019...BQ = 12/23/19.
  • The cell above each week lists the corresponding month which each Monday falls within--January, February....December. --> Rows 5-50 in Column Q has an 8-digit code (that corresponds to a line item on the P&L)

Sheet 1 - Weeks

Objective

  • Using a similar row/column structure on Sheet 2, how do I sum weeks that fall under the "January" heading from Sheet 1 into the respective 12 months on Sheet 2
  • For example, January on Sheet 1 has 5 Mondays, so I'll need to sum values on Sheet 2 under the "January" column header.

I prefer to deploy a SUMIF/INDEX/MATCH, but always appreciate elegant to alternatives. Thanks to @ScottCraner's answer HERE which got me close, but I couldn't make it work. =(

1
excelindexing

1 Answers

0
votes

Use 

=SUMIFS(INDEX(R:Z,MATCH(1111,Q:Q,0),0),$R$2:$Z$2,"January")