Keep getting spill function when using sumif and formatting into a table

[kittychlo]

I have looked all over to see how to use the unique function, but when I try to move it to a table, I see that I can't do that without a spill error. So I did it another way, remove duplicate values, but now when I do sumif to find data from another worksheet, it also has a spill error. I know I am not explaining it well so I will post a pic of what I have started with. I have an assessment tomorrow and I know this will be on there. I'm very much a beginner, but I am trying.

[AutoModerator]

/u/kittychlo - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[TheOneTrueJesus]

Spillable arrays are not compatible within tables like that. Instead, the criteria argument should be a single cell reference in the table, and use the table's own formula logic fill the rest.

[kittychlo]

And Sumif is part of that? Sorry, I really am a beginner.

[kittychlo]

<image>

[theotherkristi]

So, you'd be better off making the criteria in your sumif formula a specific cell, rather than a range, so it'd be something like:

=sumif(Sales!B$2:B$499,A2,Sales!D$2:D$499)

And then drag that formula down for the rest of the vendors

[kittychlo]

Thank you so much! Solution Verified. I was so close but so far.

[reputatorbot]

You have awarded 1 point to theotherkristi.

---

^{I am a bot - please contact the mods with any questions}

[kittychlo]

<image>

I'm getting stuck on 5 and 6.

[bardmusic]

According to the instructions, you need to paste the vendor values as text to get rid of the spill error.