Sumif and Prevsheet

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