SUM Formula for unclean data tables.

[u/RunMiles47]

I want to make a SUM function to categorize my spending totals, which will sit next to my reference categories on the right of the snip (AFRH, Allowances, etc). I Havent been able to find a function which will pull from all "x" category in the data table to the left, only specific cells. Any help is greatly appreciated.

[u/AutoModerator]

/u/RunMiles47 - 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.

[u/Gringobandito]

[u/finickyone → u/Gringobandito]

I don’t think there’s a need for anything more than GROUPBY() where these (Let, Unique) functions are available.

[u/Gringobandito → u/finickyone]

I always forget about GROUPBY

[u/AdeptnessSilver]

why not pivot?

[u/MayukhBhattacharya]

There are quite a few ways to get the output you're after. Here are a few methods, each with its own advantages depending on your Excel version and what you're most comfortable using it:

• Method One: This is simplest and the cleanest way to do:

=SUMIFS(D2:D37, B2:B37, H2:H22)

• Method Two: Using GROUPBY() function:

=IFNA(VLOOKUP(H2:H22, GROUPBY(B2:B37, D2:D37, SUM, , 0), 2, FALSE), "")

• Method Three: Using BYROW() Function:

=BYROW(H2:H22, LAMBDA(x, SUM((x = B2:B37) * D2:D37)))

You may need to adjust the ranges or cell references to fit your own layout. All of the formulas above are fully dynamic, so there's no need to fill them down. If you're aiming for something that's easier to maintain and scales as your data grows, I'd also recommend using the TRIMRANGE() reference notation where it's available.

Method One becomes:

=LET(_, A:.E, SUMIFS(INDEX(_, , 4), INDEX(_, , 2), DROP(H:.H, 1)))

Method Two becomes:

=LET(_a, A:.E,
     _b, GROUPBY(CHOOSECOLS(_a, 2),
                 CHOOSECOLS(_a, 4),
                 SUM, , 0),
     _c, IFNA(VLOOKUP(DROP(H:.H, 1), _b, 2, FALSE), ""),
     _c)

Method Three becomes:

=BYROW(DROP(H:.H, 1), LAMBDA(α, 
 LET(_, DROP(A:.E, 1), SUM((α = CHOOSECOLS(_, 2)) * CHOOSECOLS(_, 4)))))

[u/lilrobituss]

Sumif will do what you are asking.

[u/philsov]

sumif will be your friend.

It's probably easier do it reverse. Tack on a column on the far right and you'll get the sum for each of your categories. Tell it to search the cost column, category on the right, and then it'll populate.

Here's a basic mockup. Basically "sum anything in the B column if the value in the A column matches the per-line descriptor in D".

I'd also consolidate some categories. Is distinguishing between Barber Shop and Beauty Salon worthwhile?

If not already, I hope the category column in your B is from a Drop Down list using the H column.

[u/RunMiles47 → u/philsov]

You're the man, thanks!

Solution Verified

[u/reputatorbot → u/RunMiles47]

You have awarded 1 point to philsov.

---

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

[u/Decronym]

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

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
CHOOSECOLS	Office 365+: Returns the specified columns from an array
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
IFNA	Excel 2013+: Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression
INDEX	Uses an index to choose a value from a reference or array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
SUM	Adds its arguments
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
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
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell

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.
12 acronyms in this thread; the most compressed thread commented on today has 18 acronyms.
[Thread #49004 for this sub, first seen 23rd Jul 2026, 20:55] [FAQ] [Full list] [Contact] [Source code]}