How can I make this formula simpler:
=SUM('DAY1'!P39,'DAY2'!P39,'DAY3'!P39,'DAY4'!P39,'DAY5'!P39,'DAY6'!P39,'DAY7'!P39,'DAY8'!P39,'DAY9'!P39,'DAY10'!P39,'DAY11'!P39,'DAY12'!P39,'DAY13'!P39,'DAY14'!P39,'DAY15'!P39,'DAY16'!P39,'DAY17'!P39,'DAY18'!P39,'DAY19'!P39,'DAY20'!P39,'DAY21'!P39,'DAY22'!P39,'DAY23'!P39,'DAY24'!P39,'DAY25'!P39,'DAY26'!P39,'DAY27'!P39,'DAY28'!P39,'DAY29'!P39,'DAY30'!P39,'DAY31'!P39)
I am trying to get data from multiple sheets onto one page to track deals throughout the month and I'm having to copy, paste and edit the formula. In excel, this formula would be =SUM('DAY1:DAY31'!P39)
Is there a way to make this easier?
Best Answer
Step1 Create a new sheet called
AllDays
in cellB1
put=Arrayformula(if(A:A,,"'Day"&row(A:A)&"'!P39"))
Step2 In cell
C1
putStep3 Manually drag cell
C1
to the bottom of the spreadsheetNow you can simply
sum
thisAllDays
column C to get the total. Should be pretty simple to use the other columns to grab other specific cells.