Summing the values from multiple sheets with date condition

**rishiambekar** · 07-01-2019, 07:38 AM

Hello,

I am setting up the summary sheet for the investment that is being done into different schemes. I want to get this all sum of all the entries for the month across all the schemes.

So, in table against Column C for a particular month formula should add all the entries those are in the multiple sheets in the same workbook. Attaching the for working.

Also, I have highlighted the entries which should add up.

Thanks n advance.

Rushi.

**Bo_Ry** · 07-01-2019, 01:53 PM

Please try at C2
=SUM(SUMIFS(INDIRECT("sheet"&{1,2,3,4,5}&"!C2:C26"),INDIRECT("sheet"&{1,2,3,4,5}&"!B2:B26"),">="&B2,INDIRECT("sheet"&{1,2,3,4,5}&"!B2:B26"),"<"&EDATE(B2,1)))

**rishiambekar** · 07-01-2019, 11:42 PM

Bo_Ry,

Amazingly wow, It worked superbly in this sheet.

But if I have every sheet named differently like in the attached file here then formula shows error. Please suggest some solution to this...

Thanks,
Rushi.

**Bo_Ry** · 07-01-2019, 11:49 PM

Key you sheet name in H2:H6

C2
=SUMPRODUCT(SUMIFS(INDIRECT($H$2:$H$6&"!C2:C26"),INDIRECT($H$2:$H$6&"!B2:B26"),">="&B2,INDIRECT($H$2:$H$6&"!B2:B26"),"<"&EDATE(B2,1)))

**rishiambekar** · 07-02-2019, 09:20 AM

Hello,

I did as you have asked but this isn't working... Hope I am not doing anything wrong.

Could you please review the attached file once.

Thanks,
Rushi.

**Alf** · 07-02-2019, 09:57 AM

It seems like this formula don't like tab name with spaces in as well as the "&" char so try change range H2:H6 and tab name to

ABSL_Tax_relief_96
BOIAXA_Tax_Advantage
CanRob_Emerging_Eq
Kotak_Std_Multicap
L_T_Emerging_Businesses

Alf

**rishiambekar** · 07-02-2019, 10:16 AM

Hello,

This has worked. Thanks a lot!!

**Alf** · 07-02-2019, 10:36 AM

You are welcome and thanks for feedback

And hopefully Bo when he is back from the sunny beaches where he spends his time in the lap of luxury (yes I'm envious

) may have a better solution to this problem.

Alf

**Bo_Ry** · 07-02-2019, 10:42 AM

C2
=SUMPRODUCT(SUMIFS(INDIRECT("'"&$H$2:$H$6&"'!C2:C26"),INDIRECT("'"&$H$2:$H$6&"'!B2:B26"),">="&B2,INDIRECT("'"&$H$2:$H$6&"'!B2:B26"),"<"&EDATE(B2,1)))

Sheet name with space needs single quote wrap on the 'sheet name'.

**rishiambekar** · 04-30-2020, 08:22 AM

Hello,

First of all, Sorry if reopening the old thread. Don't know if it is allowed as per forum rules.

Reason I reopened this thread is because, the formula works perfect with current number of sheets. But If I add new sheet then formula doesn't add the numbers from newly added sheet. Hope if you could help in this.

Thanks a tom in advance.
Rushi.

Summing the values from multiple sheets with date condition

LinkBack

Thread Tools

Rate This Thread

Display

Summing the values from multiple sheets with date condition

Re: Summing the values from multiple sheets with date condition

Re: Summing the values from multiple sheets with date condition

Re: Summing the values from multiple sheets with date condition

Re: Summing the values from multiple sheets with date condition

Re: Summing the values from multiple sheets with date condition

Re: Summing the values from multiple sheets with date condition

Re: Summing the values from multiple sheets with date condition

Re: Summing the values from multiple sheets with date condition

Re: Summing the values from multiple sheets with date condition

Thread Information

Users Browsing this Thread

Similar Threads

Help summing values across multiple sheets with criteria

[SOLVED] Code for summing values until condition met in each cell in a range

Summing variable values across variable sheets in multiple columns

[SOLVED] Summing month to date of values in multiple columns

Summing values in named ranges based on dates, multiple sheets!

Summing of Matching Values for condition

Summing Values accross multiple sheets

Bookmarks

Bookmarks

Posting Permissions