SUMIFS Formula Reference Another Sheet
I have a budget sheet that I want to pull total monthly expenses from another sheet. The below formula is calculating the total [Health & Safety] spend, but I want it to only calculate expenses for June. I have another column which picks up the month from the date column. I am wondering if it is a SUMIFS formula and I need to add AND, but can't quite figure it out. Appreciate your help.
=SUMIF({Archived Expense Reports Range 1}, "Health & Safety", {Archived Expense Reports Range 2})
Best Answer
-
We can actually reference the Date column directly instead of needing the helper column with the month pulled out.
=SUMIFS({Archived Expense Reports Range 2}, {Archived Expense Reports Range 1}, "Health & Safety", {Date Column}, IFERROR(MONTH(@cell), 0) = 6)
The above will take into account all rows where the date is in the month of June. This does not matter which year it is in though. If you wanted to specify the year as well (June of 2021 for example), it would look like this...
=SUMIFS({Archived Expense Reports Range 2}, {Archived Expense Reports Range 1}, "Health & Safety", {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2021))
Answers
-
We can actually reference the Date column directly instead of needing the helper column with the month pulled out.
=SUMIFS({Archived Expense Reports Range 2}, {Archived Expense Reports Range 1}, "Health & Safety", {Date Column}, IFERROR(MONTH(@cell), 0) = 6)
The above will take into account all rows where the date is in the month of June. This does not matter which year it is in though. If you wanted to specify the year as well (June of 2021 for example), it would look like this...
=SUMIFS({Archived Expense Reports Range 2}, {Archived Expense Reports Range 1}, "Health & Safety", {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2021))
-
Thank you @Paul Newcome, that worked great!
-
@Paul Newcome Thank you for your help regarding the SUMIFS formula. I am using this formula for a static column and it works great. But, when I use this same formula for a referential column in which the date updates every week, it does not work. Do you have any thoughts on how I could get this to work? Thanks!
-
-
@Paul Newcome I have an order sheet that has the columns Order Cost, Projected ETA, and Revised ETA. My goal is to forecast spending for the upcoming months based on the given ETAs. Projected ETA is a static value, meaning they give this ETA once the order is placed. The Revised ETA is a new date that is given weekly, as supply chain issues are pushing many of the ETAs back (this means this column is referencing a sheet that is sent to us by our manufacturers). When I use SUMIFs on the Projected ETA column, it works perfectly and my forecasted spending is displayed by month. But when I use this same function on the Revised ETA, the only answer the formula spits out is 0. The Revised ETA does have some #NOMATCH data points, but I am using an IFERROR in my formula so I thought this would work around that issue.
I am sorry for lengthy post and confusion. Let me know if you would rather take a look at the sheet itself. Thanks!
-
@Teddyc412 You would need to use the IFERROR on the source sheet to remove the error from the source data.
-
Hello @Paul Newcome,
I am trying to create a formula similar to Michelle, but I want to add up the amount of money tied to a specific type of qualifier/category in another column. I am trying to make this table in another (metadata) sheet and create a reference to the raw data. I'm not sure what I'm missing. Currently i am getting either Unparseable or incorrect argument.
-
@Matt V. What are the formula you have tried and their errors?
-
@Paul Newcome, thanks for the reply. Was able to figure it out with the sumif, but I had to "quote" each category separately in the formula. Is that was sumifs is for? Luckily it was only about 20 items, but scaling it would be tedious.
-
@Matt V. It depends on your data structure and exactly what you are wanting to accomplish. I would need some screenshots to be able to confirm whether or not there may be a more efficient way to go about it.
-
@Paul Newcome yes I may start a new post to elaborate. Thanks again!
-
-
-
Hello, I am creating a Vacation tracker and trying to use a sumifs formula to calculate the number of days that a person has taken in a given year. The complexity is that the leave anniversaries on the start date of the employee rather than the start of the year. What I would like to do in the Vacation Days used in 2023 column is
sumif(Employee Name = Employee, Days Out, if the Vacation end date is within the last year of the anniversary date).
Sheet 1 - Leave Requests has the below columns
Employee / Supervisor / Type of Leave / Vacation Start / Vacation End / Days Out / Approved
Sheet 2 - Employee Information has the below columns
Employee / Supervisor / Date of Hire / Anniversary Date (2024) / Vacation Days Used in 2023 / Vacation Days Used in 2024 / Future Vacation Scheduled
Currently I am here but cant figure out adding the date part:
=SUMIFS({Leave Requests Days Out}, {Vacation Day Requests Employee Name}, [Employee Name]@row, {Leave Requests Approval}, "Approved")
Does anyone have any advice? Thank you all!
Help Article Resources
Categories
Check out the Formula Handbook template!
