Cross Referencing and sum formula

Frank Hammond

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.

SteyJ

https://community.smartsheet.com/discussion/comment/404542#Comment_404542

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.

SteyJ

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

Frank Hammond

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

SteyJ

https://community.smartsheet.com/discussion/comment/404542#Comment_404542

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.

Frank Hammond

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

SteyJ

https://community.smartsheet.com/discussion/comment/404546#Comment_404546

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

Frank Hammond

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

SteyJ

https://community.smartsheet.com/discussion/comment/404548#Comment_404548

Happy to help 👍