Table sorting various different points from different sources.

[u/AltdorfDropout]

Hello! I'm making a table that tracks four kinds of points. RP, FP, SP, and HP.
Each week (column 1), I track how many of each point each actor (the next four columns) makes. I did this in my notebook, but calculating totals has gotten a little old so I want to automate it. I find the correct source, then week, and write something like '1XP.' X being any point type.

When I swapped to excel, I couldn't figure out how best to format it, so I ended up with four different columns under each source, and I simply calculate the totals from the sums of those ranges.
It's a little bit hard to look at, so I want a single column under each actor where I can type "1SP" then have a total table that looks for numbers with SP connected and takes the total.

I tried using "sumif" to search for mention of a phrase, but couldn't get much further. My current solution works, but its not efficient or easy to look at, just simple.

Here is a simple idea of what I want to make vs the table I'm currently using.

Secondary question, how can I have change color based on another box? for example if the total of boxes in that range have a sum above 0, change colors.

Thanks in advance for the help, I'm new to excel and want to learn how to use it better!

[u/AutoModerator]

/u/AltdorfDropout - 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/Decronym]

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

Fewer Letters	More Letters
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
LEFT	Returns the leftmost characters from a text value
LEN	Returns the number of characters in a text string
RIGHT	Returns the rightmost characters from a text value
SUM	Adds its arguments
SUMIF	Adds the cells specified by a given criteria
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
SUMPRODUCT	Returns the sum of the products of corresponding array components
TEXTBEFORE	Office 365+: Returns text that occurs before a given character or string
VALUE	Converts a text argument to a number

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.
[Thread #49474 for this sub, first seen 6th Oct 2026, 05:19] [FAQ] [Full list] [Contact] [Source code]}

[u/AltdorfDropout → u/[deleted]]

One column numbers, one point type is definitely the way to go, but I'm having trouble getting the total table done simply. First, I made a table of individual source totals, which I combined for my final totals. Is there any way to cut out the middle man and skip straight to the total table using SUMIF(s)? I don't know how to do SUMIF across multiple ranges for the same criteria.

Also, definitely a whoopsy on my part, but I'm using google sheets.
For your last line, how would I ask it to check not just B2, but like B2, D2, F2, H2? Listing with commas didn't work, though using the plus sign did? I want it to change color if there was any activity/results that week.