Home

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

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

@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

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

 


@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?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies