Reference the same cell range on multiple sheets in a SUM.IF function

iKoenvD

New Member
Joined
Nov 2, 2022
Messages
7
Office Version
  1. 2016
Platform
  1. MacOS
Hi,

I have multiple sheets (jan, feb, mar). They all have a column (A) with (random)account numbers and a column (B) with values. I want to make a new sheet where for every account number there's a total value from all the sheets combined. So far i've got
Excel Formula:
=SUM(SUM.IF(jan!$A$2:$A$6,$A2,jan!$B$2:$B$6),SUM.IF(feb!$A$2:$A$6,$A2,feb!$B$2:$B$6),SUM.IF(mar!$A$2:$A$6,$A2,mar!$B$2:$B$6))
But this is not so elegant, especially since the real file is much more complex and has of course 12 sheets (for every month). I tried making a list of all the sheets (C1:C3) and then
Excel Formula:
=SUM.IF(INDIRECT("'"&C1:C3&"!$A$2:$A$6"),$A2,INDIRECT("'"&C1:C3&"!$B$2:$B$6"))
But this doesn't work.
I hope I explained it coherently. Any ideas?
 

Attachments

  • Schermafbeelding 2023-07-11 om 14.59.43.png
    Schermafbeelding 2023-07-11 om 14.59.43.png
    20.5 KB · Views: 14
  • Schermafbeelding 2023-07-11 om 14.59.35.png
    Schermafbeelding 2023-07-11 om 14.59.35.png
    20.3 KB · Views: 15
  • Schermafbeelding 2023-07-11 om 14.59.19.png
    Schermafbeelding 2023-07-11 om 14.59.19.png
    20.3 KB · Views: 15
  • Schermafbeelding 2023-07-11 om 15.01.32.png
    Schermafbeelding 2023-07-11 om 15.01.32.png
    20.9 KB · Views: 21

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You can always merge all the sheets with Power Query (Get & Transform) and apply a SUMIF function or a Pivot Table
 
Upvote 0
I'm not sure how that works. Excel says I would have to install some extra software. I would rather not if there is another way.
 
Upvote 0

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top