Sumif and Prevsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-928185%22%20slang%3D%22en-US%22%3ESumif%20and%20Prevsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-928185%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20finally%20go%20the%20Prevsheet%20to%20work%2C%20and%20it%20works%20beautifully.%20HOWEVER%2C%20when%20I%20use%20it%20in%20a%20Sumif%20formula%20it%20will%20not%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20statement%20that%20returns%20an%20error%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIF(PrevSheet(%24I%3A%24I)%2C%22%3Du%22%2CPrevSheet(F%3AF))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20I%20don't%20use%20the%20PrevSheet%2C%20it%20works%20great.%26nbsp%3B%20This%20is%20the%20formula%20that%20works%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIF('Oct%2018%20-%20Oct%2024'!%24I%3A%24I%2C%22%3Du%22%2C'Oct%2018%20-%20Oct%2024'!F%3AF)%2BSUMIF('Oct%2018%20-%20Oct%2024'!%24I%3A%24I%2C%22%3Du%22%2C'Oct%2018%20-%20Oct%2024'!G%3AG)*-1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeedless%20to%20say%20the%20former%20formula%20would%20be%20ideal%2C%20but%20I%20just%20can't%20make%20it%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-928185%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-930575%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20and%20Prevsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-930575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328864%22%20target%3D%22_blank%22%3E%40Trisa58%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20missed%20the%20exclamation%20symbol%20next%20to%20the%20sheet%20reference%20in%20the%20formula%20and%20you%20shouldn't%20use%20parenthesis%20around%20the%20column%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20should%20try%20it%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMIF(PrevSheet!%24I%3A%24I%2C%22u%22%2CPrevSheet!F%3AF)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3A%20%3C%2FSTRONG%3EIf%20the%20Sheet%20name%20referred%20in%20the%20formula%20contains%20a%20space%20in%20it's%20name%2C%20you%20must%20surround%20the%20Sheet%20Name%20with%20single%20quotes.%20e.g.%20if%20the%20Sheet%20name%20is%20Prev%20Sheet%2C%20the%20same%20formula%20should%20be%20written%20as...%3C%2FP%3E%3CP%3E%3DSUMIF(%3CSTRONG%3E'Prev%20Sheet'!%3C%2FSTRONG%3E%24I%3A%24I%2C%22u%22%2C%3CSTRONG%3E'Prev%20Sheet'!%3C%2FSTRONG%3EF%3AF)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-947722%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20and%20Prevsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-947722%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20doesn't%20work%2C%20the%20name%20of%20the%20sheet%20isn't%20%22prevsheet%22.%26nbsp%3B%20The%20Prevsheet%20is%20a%20function%20created%20using%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20tried%20your%20suggestion%20it%20returns%20an%20error%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-947987%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20and%20Prevsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-947987%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328864%22%20target%3D%22_blank%22%3E%40Trisa58%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPrevSheet%20is%20not%20built-in%20function%20and%20there%20are%20few%20variants%20of%20it%20published%20on%20web.%20VBA%20people%20could%20help%20if%20you%20share%20the%20code%20or%20at%20least%20link%20on%20your%20variant%20of%20the%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-951275%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20and%20Prevsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-951275%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328864%22%20target%3D%22_blank%22%3E%40Trisa58%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20doesn't%20work%2C%20the%20name%20of%20the%20sheet%20isn't%20%22prevsheet%22.%26nbsp%3B%20The%20Prevsheet%20is%20a%20function%20created%20using%20VBA.%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EWhat%20does%20the%20Prevsheet%20function%20return%3F%20Can%20you%20please%20share%20the%20code%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

So I finally go the Prevsheet to work, and it works beautifully. HOWEVER, when I use it in a Sumif formula it will not work.

 

This is the statement that returns an error:

 

=SUMIF(PrevSheet($I:$I),"=u",PrevSheet(F:F))

 

But if I don't use the PrevSheet, it works great.  This is the formula that works:

 

=SUMIF('Oct 18 - Oct 24'!$I:$I,"=u",'Oct 18 - Oct 24'!F:F)+SUMIF('Oct 18 - Oct 24'!$I:$I,"=u",'Oct 18 - Oct 24'!G:G)*-1

 

Needless to say the former formula would be ideal, but I just can't make it work.

 

Thoughts?

 

4 Replies
Highlighted

@Trisa58 

You missed the exclamation symbol next to the sheet reference in the formula and you shouldn't use parenthesis around the column reference.

 

You should try it like this...

 

=SUMIF(PrevSheet!$I:$I,"u",PrevSheet!F:F)

 

Note: If the Sheet name referred in the formula contains a space in it's name, you must surround the Sheet Name with single quotes. e.g. if the Sheet name is Prev Sheet, the same formula should be written as...

=SUMIF('Prev Sheet'!$I:$I,"u",'Prev Sheet'!F:F)

Highlighted

@Subodh_Tiwari_sktneer 

That doesn't work, the name of the sheet isn't "prevsheet".  The Prevsheet is a function created using VBA.

 

When I tried your suggestion it returns an error

Highlighted

@Trisa58 

PrevSheet is not built-in function and there are few variants of it published on web. VBA people could help if you share the code or at least link on your variant of the function.

Highlighted

 


@Trisa58 wrote:

@Subodh_Tiwari_sktneer 

That doesn't work, the name of the sheet isn't "prevsheet".  The Prevsheet is a function created using VBA.


What does the Prevsheet function return? Can you please share the code?