SOLVED

SUMIF INDIRECT #REF Error

%3CLINGO-SUB%20id%3D%22lingo-sub-2678253%22%20slang%3D%22en-US%22%3ESUMIF%20INDIRECT%20%23REF%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2678253%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20currently%20struggling%20with%20performing%20a%20SUMIF%20INDIRECT%20formula%20as%20can%20be%20seen%20in%20the%20below%20linked%20youtube%20video%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DOM4MAaQ4oN0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DOM4MAaQ4oN0%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20aim%20is%20to%20sum%20the%20total%20of%20each%20category%20(Bill%2FPayment%2FJournal%2FCurrency%20Revaluation)%20using%20the%20tab%20name%20as%20a%20reference%20in%20the%20SUMIF%20formula%20(attached%20excel%20sheet%20is%20a%20shortened%20version%2C%20this%20has%20to%20be%20replicated%20across%20multiple%20tabs)%2C%20however%20it%20is%20returning%20a%20%23REF%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20you%20can%20give%20will%20be%20really%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Kpavey_0-1629794482982.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305325i54FDDB6A8FD4A97A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Kpavey_0-1629794482982.png%22%20alt%3D%22Kpavey_0-1629794482982.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKieran%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2678253%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-2678329%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20INDIRECT%20%23REF%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2678329%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1135996%22%20target%3D%22_blank%22%3E%40Kpavey%3C%2FA%3E%26nbsp%3BInclude%20single%20quotes%20around%20the%20sheet%20names.%20Needed%20since%20these%20contain%20a%20special%20character.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDIRECT(%22%3CFONT%20color%3D%22%23FF0000%22%3E'%3C%2FFONT%3E%22%20%26amp%3B%20%24B%243%20%26amp%3B%20%22%3CFONT%20color%3D%22%23FF0000%22%3E'%3C%2FFONT%3E!B%3AB%22)%26nbsp%3Bor%20change%20the%20content%20of%20cell%20B3%20to%3CSTRONG%3E%20'1293-103'%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20should%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I am currently struggling with performing a SUMIF INDIRECT formula as can be seen in the below linked youtube video:

https://www.youtube.com/watch?v=OM4MAaQ4oN0

 

My aim is to sum the total of each category (Bill/Payment/Journal/Currency Revaluation) using the tab name as a reference in the SUMIF formula (attached excel sheet is a shortened version, this has to be replicated across multiple tabs), however it is returning a #REF error. 

 

Any help you can give will be really appreciated.

 

Kpavey_0-1629794482982.png

 

Thanks,

 

Kieran

In this video, we look at using the SUMIF function with the INDIRECT function for dynamic worksheet and table references.Learn it all with the Ultimate Excel...
2 Replies
best response confirmed by Kpavey (New Contributor)
Solution

@Kpavey Include single quotes around the sheet names. Needed since these contain a special character.

 

=INDIRECT("'" & $B$3 & "'!B:B") or change the content of cell B3 to '1293-103'

 

That should work.

Perfect, that's helped a lot thanks very much!