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!
