Date range based Sum Up from multiple work sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1721874%22%20slang%3D%22en-US%22%3EDate%20range%20based%20Sum%20Up%20from%20multiple%20work%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1721874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EHi%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20have%20a%20work%20book%20with%2026%20sheets.%2C%20where%20Jumbo1%20is%20sheet%20one%20and%20Boeing1%20is%20sheet%2026.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EAll%2026%20sheets%20are%20with%20identical%20column%20and%20data.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20want%20formula%2C%20to%20add%20up%20amounts%20collected%2C%20for%20a%20month%20from%20the%20date%20I%20enter%20in%20H11.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ESo%2C%20Input%20is-%20start%20date%20into%20H11.%20I%20want%20to%20get%20a%20return%20value%2C%20which%20is%20total%20amount%20collected%20from%20all%2026%20sheets%2C%20for%20one%20month%2C%20from%20the%20date%20I%20enter.%20Hope%20it%20make%20sense.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20use%20this%20formula%2C%20%3CSPAN%3E%3CFONT%20color%3D%22%23008000%22%3E%3DSUMIFS(D3%3AD34%2CC3%3AC34%2C%22%26gt%3B%22%26amp%3BH11)%3C%2FFONT%3E%26nbsp%3B%3C%2FSPAN%3Ewhich%20work%20on%20one%20sheet.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20tried%20this%26nbsp%3B%3CSPAN%3E%3CFONT%20color%3D%22%23FF6600%22%3E%3DSUMIFS('Jumbo1%3ABoing9'!D3%3AD34%2C'Jumbo1%3ABoeing9'!C3%3AC34%2C%22%26gt%3B%22%26amp%3BH11)%3C%2FFONT%3Ebut%20giving%20me%20%3CFONT%20color%3D%22%23000000%22%3E%23value%3C%2FFONT%3E%20error.%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3ECan%20anyone%20help%20me%20to%20correct%20it%20or%20with%20a%20new%20one.%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3EThanks%20in%20advance.%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1721874%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1721983%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20range%20based%20Sum%20Up%20from%20multiple%20work%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1721983%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F812316%22%20target%3D%22_blank%22%3E%40Ben1022%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreate%20a%20list%20of%20the%20sheet%20names%20Jumbo1%20...%20Boeing9%20in%20a%20column%20on%20the%20same%20sheet%20as%20the%20formula%2C%20say%20in%20N1%3AN26.%20You%20can%20then%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(SUMIFS(INDIRECT(%22'%22%26amp%3BN1%3AN26%26amp%3B%22'!D3%3AD34%22)%2CINDIRECT(%22'%22%26amp%3BN1%3AN26%26amp%3B%22'!C3%3AC34%22)%2C%22%26gt%3B%22%26amp%3BH11))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1722026%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20range%20based%20Sum%20Up%20from%20multiple%20work%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1722026%22%20slang%3D%22en-US%22%3EThanks%20Hans%2C%20I%20will%20try.%20Someone%20on%20FB%20gave%20me%20that%20idea%2C%20but%20it%20giving%20me%20false%20total.%20This%20was%20the%20formula%20%3DSUM(SUMIFS(INDIRECT(A1%3AA26%26amp%3B%22!D3%3AD34%22)%2CINDIRECT(A1%3AA26%26amp%3B%22!C3%3AC34%22)%2C%22%26gt%3B%22%26amp%3BH11))%3CBR%20%2F%3EI%20don't%20know%20how%20to%20upload%20a%20pic%20here%2C%20otherwise%20it%20was%20easy%20with%20ascreen%20shot.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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.

Thanks in advance.

 

 

6 Replies
Highlighted

@Ben1022 

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))

Highlighted
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.
Highlighted

@Ben1022 

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

Highlighted
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.

Highlighted

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

Highlighted

@JMB17 

Thanks,

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