New Contributor

# Date range based Sum Up from multiple work sheet

Hi,

I have a work book with 26 sheets., where Jumbo1 is sheet one and Boeing9 is sheet 26.

All 26 sheets are with identical column and data.

I want a formula, to add up amounts collected, for a month from the date I enter in H11.

So, Input is- start date into H11. I want to get a return value, which is total amount collected from all 26 sheets, for one month, from the date I enter. Hope it make sense.

I use this formula, =SUMIFS(D3:D34,C3:C34,">"&H11) which work on one sheet.

I tried this =SUMIFS('Jumbo1:Boing9'!D3:D34,'Jumbo1:Boeing9'!C3:C34,">"&H11)but giving me #value error.

Can anyone help me to correct it or with a new one.

6 Replies

# Re: Date range based Sum Up from multiple work sheet

Create a list of the sheet names Jumbo1 ... Boeing9 in a column on the same sheet as the formula, say in N1:N26. You can then use

=SUMPRODUCT(SUMIFS(INDIRECT("'"&N1:N26&"'!D3:D34"),INDIRECT("'"&N1:N26&"'!C3:C34"),">"&H11))

# Re: Date range based Sum Up from multiple work sheet

Thanks Hans, I will try. Someone on FB gave me that idea, but it giving me false total. This was the formula =SUM(SUMIFS(INDIRECT(A1:A26&"!D3:D34"),INDIRECT(A1:A26&"!C3:C34"),">"&H11))
I don't know how to upload a pic here, otherwise it was easy with ascreen shot.

# Re: Date range based Sum Up from multiple work sheet

Save your screenshot to disk, then drag the picture file from File Explorer / Finder into the reply box here.

# Re: Date range based Sum Up from multiple work sheet

Did you sumif each sheet individually and compare to your manual calculation for each sheet to identify which worksheet(s) were causing the variance? It may be an issue with the data or your manual calculation is off.

If you can upload the actual workbook, it would be more helpful than a screenshot.

# Re: Date range based Sum Up from multiple work sheet

@Hans Vogelaar  This is my practice sheet. So the sheet names are different, but the same in effect.

# Re: Date range based Sum Up from multiple work sheet

Thanks,

I will do an individual sheet ones today and update the result.