Hi everyone,
Every month I get this table (Clients) where I am given costs broken out by rows of clients and columns of weeks. However, I need to summarize these values by Client and Month instead of week (Sheet2).
Pivot tables don't seem to read across columns and my current sumproduct formula is ok but requires having to manually select the range of weeks that correspond to the month.
Would anyone know a better way to summarize this data by Month that doesn't require manually selecting the range of columns that correspond? Is such a thing possible?
Please see attached pictures for the client data (Client) and the summary sheet (Sheet2) with the current formula shown in the formula bar
.
thanks!
Every month I get this table (Clients) where I am given costs broken out by rows of clients and columns of weeks. However, I need to summarize these values by Client and Month instead of week (Sheet2).
Pivot tables don't seem to read across columns and my current sumproduct formula is ok but requires having to manually select the range of weeks that correspond to the month.
Would anyone know a better way to summarize this data by Month that doesn't require manually selecting the range of columns that correspond? Is such a thing possible?
Please see attached pictures for the client data (Client) and the summary sheet (Sheet2) with the current formula shown in the formula bar
Excel Formula:
=SUMPRODUCT((Clients!$A$5:$A$22=Sheet2!$B3)*Clients!$B$5:$E$22)thanks!
