Search code examples
google-apps-scriptgoogle-sheetssumifs

Sum if in multiple sheets in Google Sheets

I am looking for funcion/code for Google Sheet document which would enable me to sum hours (numbers) on multiple sheets based on names.

Basically I need this to create a prediction tool for project hours allocation to control potential overbooking of hours.

Here's link to the file: https://docs.google.com/spreadsheets/d/1p-MGD1E7uj7_wkGoDPDH_Qgc-jRKJH55d2TvMG9VsBk/edit?usp=sharing

Sheets will be regurarly updated.

I've tried to use INDIRECT funcion but I see that doesn't work in this case.

Solution

  • Try with this function: I've modified the location of your sheets' names since it would be overlapped if you had more names. With MAKEARRAY it creates the chart counting the names, and with REDUCE it sums the values of each sheet you have in your range. Adapt the ranges accordingly to your source sheet!

    =MAKEARRAY(COUNTA(A6:A),COUNTA(B5:5),LAMBDA(r,c,
REDUCE(,A2:B4,LAMBDA(a,b,a+  IFERROR(INDEX(INDIRECT("'"&b&"'!B4:M"),  MATCH(INDEX(A6:A,r),INDIRECT("'"&b&"'!A4:A"),0),  MATCH(INDEX(B5:5,,c),INDIRECT("'"&b&"'!B3:3"),0)))))))

    enter image description here

    NOTE: Be aware of having matching names in your cells with your actual sheets. You had 'Project 1' instead of 'Project1'