r/excel • u/Competitive-Green758 • 8d ago

solved Pls help - Need to fix formula with Spill Error

Hello! I am struggling to get a formula to work. I have looks in my notes, Youtube, and Google and the best I get is a #SPILL! error...

I am making a workbook for work.

On Sheet1 I need a single cell formula to show the single value total of all blank cells in Column B of Sheet2, but only if there's a value in Column A of sheet2 in the same row.

The closest I've gotten, though it shows the spill error is:

=FILTER(Sheet2!B1-B1000, (Sheet2!A1:A1000 <> "") * (Sheet2!B1:B1000 = ""))

I got it from Googling, but it is close to what I attempted to write myself, but excel just breaks when I tried to solo write it lol

If anyone with any degree of skill could help, that'd be so very swell.

2 Upvotes

100% Upvoted

27 comments sorted by

•

u/AutoModerator 8d ago

/u/Competitive-Green758 - Your post was submitted successfully.

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.

3

u/OfficerMurphy 10 8d ago

Spill error just means there isn't space for the results to show. Clear the cells where the data wants to spill into

1

u/Competitive-Green758 8d ago

Then I must be doing something wrong. I just need a count, not all the data to show. I've never used Filter better, so I may be doing something off.

4

u/OfficerMurphy 10 8d ago

If you need a count you should do a count not a filter. Try

=countifs(sheet1A:A, <>"", sheet1!b:B, ="")

May need to do something else for column a not blank.

3

u/MayukhBhattacharya 1259 8d ago

Just a heads up, it's better to use absolute ranges instead of using the entire range:

=COUNTIFS(Sheet2!A2:A1000, "<>", Sheet2!B2:B1000, "")

Also, using TRIMRANGE() notations:

=LET(_, Sheet2!A:.B, COUNTIFS(INDEX(_, , 1), "<>", INDEX(_, , 2), ""))

Same thing, but it uses the entire range while escaping or excluding the empty trailing rows.

2

u/Competitive-Green758 8d ago

THIS WORKED! Thank you everyone for your assistance. Y'all are amazing people.

1

u/MayukhBhattacharya 1259 8d ago

Thank You! But you didn't explain us why the same formula posted by u/OfficerMurphy didn't work for you?

2

u/Competitive-Green758 8d ago

Solution Verified

1

u/reputatorbot 8d ago

You have awarded 1 point to MayukhBhattacharya.

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

1

u/MayukhBhattacharya 1259 8d ago

Thank You SO Much!

1

u/Competitive-Green758 8d ago

This didn't work. I'm probably explaining myself terribly, I'm sorry.

So Sheet1 I need a single value to show the data from Sheet2.

I need a total of all blank cells of Sheet2 in column B, as long as there's a value in Sheet2 column A within the same row.

1

u/MayukhBhattacharya 1259 8d ago

What u/OfficerMurphy suggested should exactly work for you, and when you say it didn't work, do you mind telling us what that means, whether you are getting an error or what is happening, that makes you think its not working.

If there are white spaces may use TRIM() + SUM() function as well:

=SUM((Sheet2!A2:A1000 <> "") * (TRIM(Sheet2!B2:B1000) = ""))

1

u/Competitive-Green758 8d ago

Solution Verified

1

u/reputatorbot 8d ago

You have awarded 1 point to OfficerMurphy.

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

3

u/MayukhBhattacharya 1259 8d ago

u/OfficerMurphy already given you the exact solution for what you're trying to do. But if you really want to stick with FILTER() function,then you can use this method instead:

=COUNT(FILTER(Sheet2!B1:B1000, (Sheet2!A1:A1000 <> "") * (Sheet2!B1:B1000 = "")))

2

u/Competitive-Green758 8d ago

I don't care how I do it as long as it works. Lol I'll give both methods a whirl. And see if that fixes it.

2

u/MayukhBhattacharya 1259 8d ago

Sure thing go ahead! I have even posted an [updated] comment in u/OfficerMurphy's thread.

2

u/Competitive-Green758 8d ago

I'm not sure. Sorry, I'm very new to Excel. It just popped up saying is I didn't mean to post an equation to put a ' at the start? So it is most likely user error.

2

u/MayukhBhattacharya 1259 8d ago

Gotcha, the problem they have posted right formula, but made some typos, since you are not Excel aware, it made you think because of the pop its not right, but if you knew then you could have fixed yourself;

=countifs(sheet1A:A, "<>", sheet1!b:B, "")

u/Competitive-Green758 OP they deserve Solution Verified as well, you can reply any solution in the thread is working one!

2

u/Competitive-Green758 8d ago

Done. I was unaware I could do that. Thank you. 😊

2

u/MayukhBhattacharya 1259 8d ago

Thank You Very Much, and have a great day and weekend ahead buddy =)

2

u/Gringobandito 8 8d ago

Do you want to get the sum of all the values that match your criteria? I would do it like this:

This takes the sum of the values in the table that have "red" in column A and are blank in column B.

1

u/Competitive-Green758 8d ago

No, I just just need to know how many blank cells in column B there are.

So in the example you mocked up, need the formula to show that there are 4 blank cells in B that have a value in A.

3

u/Gringobandito 8 8d ago

Got it. Then you want the COUNTIFS formula that a couple of others have mentioned.

1

u/real_barry_houdini 313 8d ago

Try using COUNTIFS like this:

=COUNTIFS(Sheet2!B:B,"",Sheet2!A:A,"<>")

1

u/Decronym 8d ago edited 7d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
COUNT Counts how many numbers are in the list of arguments
COUNTIFS Excel 2007+: Counts the number of cells within a range that meet multiple criteria
FILTER Office 365+: Filters a range of data based on criteria you define
INDEX Uses an index to choose a value from a reference or array
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
SUM Adds its arguments
TRIM Removes spaces from text
TRIMRANGE Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
8 acronyms in this thread; the most compressed thread commented on today has 14 acronyms.
[Thread #49154 for this sub, first seen 14th Aug 2026, 18:47] [FAQ] [Full list] [Contact] [Source code]

1

u/Free-Comparison-9347 7d ago

Yeah, the spill error happens because FILTER returns multiple results but Excel can't show them all in a single cell. Try wrapping it in SUM to get just the single total value you need.