May 12 2023 11:24 AM
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!
May 12 2023 11:47 AM
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:
SUMIF(Category Range, What you want to sum. So if you want it to sum Advertising amount you put "Advertising", Amount Range)
Category Range: represents the column that has all of your categories, I would just highlight the entire column: A,B,C,etc.
For the second portion, include the category you want to find the sum of AND PUT IT IN QUOTES.
Amount Range: Represents the column that has all of your Amounts, I would just highlight the entire column: A,B,C,etc.
May 12 2023 12:20 PM
May 12 2023 12:33 PM
May 12 2023 01:02 PM
May 12 2023 01:40 PM
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.
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.
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.
Please let me know if this works and if I misunderstood, I would still love to help!
May 13 2023 09:29 AM