Auto-Populate Data in Cells Based on Category Totals

Question

I'm not great at Excel, so I bet there is an easy way to do this and I'm just not seeing it online anywhere or Googling the correct things!

I track my marketing invoices for work in an Excel sheet. I categorize or code each invoice for payment distribution according to my department's budget. I would like to auto-populate data that shows me the total amount spent in each category in real time. Each time I add an invoice, I'd like each category column to populate the total amount of all money for that category so I don't have to sit down and manually add each one together. Please see my example below!

Data Table

Date	Amount	Category
5/13/23	$10.00	Advertising
5/13/23	$10.00	Social Media
5/13/23	$35.00	Social Media

Category Table

Advertising Total Spend	Social Media Total Spend
$10.00	$45.00

I want to take the total amount for each category in the Data Table and place it, where it will automatically total itself up for each new addition, in the Category Table.

I hope that all makes sense! Thanks in advance!

karma21237 · Answer

DarbyNap&nbsp;Let me know if I understand your question. You want to find the total amount for each category: Advertising, Social Media, etc. What I would do is use the SUMIF() function. For this function, you would do:&nbsp;SUMIF(Category Range, What you want to sum. So if you want it to sum Advertising amount you put "Advertising", Amount Range)&nbsp;Category Range: represents the column that has all of your categories, I would just highlight the entire column: A,B,C,etc.&nbsp;For the second portion, include the category you want to find the sum of AND PUT IT IN QUOTES.&nbsp;&nbsp;Amount Range: Represents the column that has all of your Amounts, I would just highlight the entire column: A,B,C,etc.&nbsp;

darbynap · Answer

Thanks a lot! I think that the SUMIF function might be the solution? But, I'd like to keep the invoices organized by date, which means that the range portion would be staggered. So, I might have three Social Media invoices, and then two Advertising invoices, and then another Social Media invoice. Does this roadblock make sense? I want to send a screen shot of my sheet, but I can't figure out how to do it! The upload image option here isn't as simple as just uploading an image. I hope my response makes sense.

karma21237 · Answer

So you want to have everything sorted by the date is that correct? Sure! Click Home tab > arrow under Sort & Filter (3rd from most right), and then click Sort Oldest to Newest, or Sort Newest to Oldest.

Let me know if this helps!

darbynap · Answer

Sorry, I don't think I'm explaining it correctly! 🙂 You're being very helpful but without being able to attach a screenshot I don't think I'm making great sense here. I know how to sort by date, but I don't understand the SUMIF function completely as it relates to me having to select a range. Let me try again! It might be as simple as me needing to set this up differently, but I don't see how.Manually-Entered Data ColumnsSo, I have Columns A-G filled with data. The column headers include information about the date, the total amount of the invoice, the company, the necessary accounting information, and the billing code, (or category) among other things. Column A contains the date, and Column B contains the amount. Column G contains the billing code. I manually enter this information in the order it's received from the companies billing me, which means they're automatically in order by their date, with the newest at the bottom. That means that Column G, which contains the billing code, really varies quite a bit. There could be a billing code for Advertising that's the same 5 times in a row, because I have five invoices for Advertising that I received five times in a row. Then one day I'll get an invoice for Web Development, or Social Media, let's say. That billing code is different. So there will be five Advertising billing codes, followed with one Social Media billing code and then two Web Development billing codes. Then maybe two more Advertising invoices, followed by another Social Media billing code invoice, but there will never be a situation where there are a consistent set of billing codes not broken up by others. I hope that makes sense. Plus, information is always being added, so I would have to constantly re-select a range (if I understand the SUMIF function correctly, which I may not) every time I am adding a new invoice to the data columns.Category ColumnsNow, Columns J-M have four categories labeled on them as headers, which are the billing codes (one related to advertising, one social media for example, etc.). These are the columns I'd want to contain the auto-populated data. So under Column J, in the cell directly below the category label, I'd want to have Excel auto-populate the total amount of money we've spent on any of these categories, with the information being taken from the Manually-Entered Data Columns. It sounds like the SUMIF function makes sense here, but I'm not understanding how I'd use it if I have to select a range. I am constantly adding to this spreadsheet manually, and the column that contains the billing codes varies wildly. There are several different ranges of the same billing code. I have a total of 31 invoices I've entered since January 3, 2023 and expect to more than double this before the end of the year.I'd like to see the total amount spent per category updated in real time, each time I add a new invoice. It is quite possible that this isn't a function I suppose, but I would be shocked if that's true!It is also quite possible that your answer with SUMIF is correct! Am I not understanding the SUMIF function properly? I am not sure what the range I need to select is. This is what Excel prompts me to include when I type =SUMIF: (range, criteria, [sum_range]). I hope that my additional and more detailed information helps.Thanks for assisting. 🙂

karma21237 · Answer

DarbyNap&nbsp;Hopefully I understand it this time. Sorry sometimes I just have trouble understanding things and for your last message I completely missed a portion of it.&nbsp;&nbsp;From my perspective (and please let me know if I am wrong because I would love to help!), you are worried about the range as when you select the data you input, lets say rows 1-30, when you add in new data at row 31 for example, it will not be included. Is that correct? This will also not be organized by the subject of invoice so it will be staggered from Social Media to Advertising etc.&nbsp;&nbsp;If this is the case, then when putting in the array for the range, instead of doing lets say A1:A30, do A:A or click the letter at the top of the column to be your range. By doing this, even when you add in more data, it will account for this as doing A:A accounts for everything that is put in that column. Also when doing this, it will look at everything and even with all of the invoice subjects being randomly scattered in there, it will only contain what you need.&nbsp;&nbsp;Please let me know if this works and if I misunderstood, I would still love to help!

Forum Discussion

Auto-Populate Data in Cells Based on Category Totals

Share

Resources