Cross Referencing and sum formula
Hi all,
I am working on this new sheet, and have successful formulas from previous sheets that work great. However, I have hit a roadblock here on this one.
This formula looks at the Errors sheet I have, and a column that produces a 1 if there is data in the column next to it. With that, I have a consolidation sheet that counts the points based on a certain criteria. It is only producing a zero instead of summing.
=IF(ISBLANK(Agent@row), "", SUMIFS({Errors Range 10}, {Errors Range 10}, @cell = Agent@row))
The Agent@row is polling the specific information from the Errors form and consolidated the Agent to one indexed cell. The point of this is to look for all the instances of that agent on the other form and sum up the values of the noted errors.
I hope this makes sense LOL. Thanks for any assistance on this.
Best Answer

Oh, your issue is that you need to change the errors to match with the agent, and also change the logic of the IF.
So now we have:
=IF(agent@row = "", "", SUMIFS({Errors Range}, {Agent}, agent@row))
So, if agent is blank, blank returns. If agent is not blank, SUMIFS function occurs to tally the errors range based on the {agent reference} matching with agent@row.
Sincerely,
Jacob Stey
Answers

Make sure you are converting the counted SUM of errors to a value using =VALUE(formula to count errors here). I have noticed that this helps when trying to SUM.
I suggest not using the ISBLANK function, as it is quicker to just consolidate with <> which is the same thing but without the function.
=IF(Agent@row <> "", "", SUMIFS({Errors Range 10}, {Errors Range 10}, @cell = Agent@row))
Sincerely,
Jacob Stey

Stey, thanks for the tip on the <> function.
Now to the meat and potatoes. The formula produced nothing. I ended up cleaning up the references and moved it to Range 2.
This is what Range 2 is on the Errors sheet

Oh, your issue is that you need to change the errors to match with the agent, and also change the logic of the IF.
So now we have:
=IF(agent@row = "", "", SUMIFS({Errors Range}, {Agent}, agent@row))
So, if agent is blank, blank returns. If agent is not blank, SUMIFS function occurs to tally the errors range based on the {agent reference} matching with agent@row.
Sincerely,
Jacob Stey

came back as invalid reference. Here is the original formula I used on another sheet that works flawlessly. It is the same concept.
=IF(ISBLANK(Agent@row), "", SUMIFS({Battle Pass Submission Form Range 3}, {Battle Pass Submission Form Range 1}, @cell = Agent@row))

Invalid reference just means you need to update the reference, then it should be working fine
Sincerely,
Jacob Stey

I am an idiot. LOL, I never did anything with the {Agent} bit. Thanks for your assistance on this one, Jacob.
=IF(Agent@row = "", "", SUMIFS({Errors Range 2}, {Agent}, Agent@row))

Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!