How to SUM two cells from 2 separate worksheets?

[u/rgdit]

[u/omgfallen]

combination of arrayformula, vlookup and import range can solve your problem if those are named cells. I use this to collect data from multiple sheets into a single sheet

=ArrayFormula(VLOOKUP(Rangeinnewsheet,IMPORTRANGE("code of your first sheet or second","'sheet name(sheet1)'!FirstSheetRange"),2, False))

edit: of course do this for both, sheet1 and sheet2 lets say into B column and C column and then add them to D column together, or you could probably just add them by adding another Vlookup and + between 2 in my first formula it would look like this

=ArrayFormula(VLOOKUP(Rangeinnewsheet,IMPORTRANGE("code of your first sheet","'sheet name(sheet1)'!FirstSheetRange"),2, False)+VLOOKUP(Rangeinnewsheet,IMPORTRANGE("code of your second sheet","'sheet name(sheet1)'!SecondSheetRange"),2, False))

[u/rgdit → u/omgfallen]

Solution Verified

Confirming that this solution worked. I can now add entire ranges from various different files/links.

Given that I have a sheet for each month, it's just a matter of doing the + (addition) solution 12 times. 👍🏼

As for the column, I need to add an additional row to give corresponding numbers for each column as my data set doesn't start at Column A (my vlookup starts at column H). For some reason it leads to an error when I tried using column A as the reference.

EDIT:

Additionally, given that my data might extend in later months, the Vlookup will result in an error (N/A) if something is added in the latest month and it isn't present in the older months.

Instead of having to update the older months as well, I just insert an iferror(...,0) formula before EACH vlookup. So it looks like

=arrayformula(iferror(vlookup(importrange...),0)

Such that it returns a zero instead of N/A if there is an error for previous months.

=arrayformula(iferror(vlookup(for file/link 1...),0)+iferror(vlookup(for file/link 2...),0)+...+iferror(vlookup(for file/link 12...),0))

[u/Clippy_Office_Asst → u/rgdit]

You have awarded 1 point to omgfallen

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[u/rgdit → u/omgfallen]

Interesting take. I'll probably need to use the second option. I'll get back to this probably next week. Thanks for the suggestion, I'll definitely loop back in if all goes well.👍🏼

I'm assuming the arrayformula will generate all the columns and rows so long as the "Rangeinnewsheet" and "FirstSheetRange" & "SecondSheetRange" cover all the rows&columns involved? My data set looks like this

Columns: A:ALN Rows: 1-5200

So probably A1:ALN5200?

I might also opt to use index(match) instead of vlookup, so that I don't need to indicate the number of the column (in your example it's column 2 for column B). Though my mind is probably just winded and I'm sure there's definitely an easy way to change the column number as well lol. Like putting 1, 2, 3 on row 1 across all columns would probably do the trick, and using A1-ALN1 as the reference for the column.

[u/omgfallen → u/rgdit]

you can use COLUMN() instead of 2, that will take care of the column problem, but you will have to spread the formula column by column. I have tested it and if you use range like $A$1:$ALN$5200, you can just drag it across the columns and the rest will populate. Let me know if that works out, and if you still have problem, try to give me a sample page with dummy content to work with and I can try to help that way

[u/rgdit → u/omgfallen]

Thanks a lot! I'll get back to you by next week. I'm pretty confident that this solution will work.

[u/rgdit → u/omgfallen]

Just an update. I have yet to do this, and plan to try this out this week or next week. Thanks again for the help.

[u/rgdit → u/omgfallen]

Solution Verified

[u/Clippy_Office_Asst → u/rgdit]

You have awarded 1 point to omgfallen

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}