Sum of Totals by area and Date

Walter Mootz

I am trying to set up a formula that does the following:

If in Work Log A, the Plant is X and the date range is between 10-1-2020 and now, what is the total cost of this time range for this sheet?

I also want to break down by monthly range:

If in Work Log A the plant is X and the date range is between 1-1-2021 and 1-31-2021, what is the total cost of this time range for this sheet?

I have similar formulas for the references in the other sheets, but what's hanging me up is trying to specify a date range within the formula.

Paul Newcome

No. The @cell is as it should be. It looks like you have a period after the first @cell reference though. Try removing that. You are also going to want GREATER THAN or equal to for your first date argument, but you only have equal to.

Paul Newcome

Try something along the lines of...

=SUMIFS({Source Sheet Column To Sum}, {Source Sheet Plant Column}, "Plant X", {Source Sheet Date Column}, AND(@cell >= DATE(2020, 10, 01), @cell <=TODAY()))

EDITED to correct a typo in the formula

Walter Mootz

I got an Unparseable error for the formula with that formula. Here is a screenshot of it:

Do i need to put a specific cell reference for @cell? if so, what should i do when i want the total of everything that meets the date and plant criteria?

Paul Newcome

No. The @cell is as it should be. It looks like you have a period after the first @cell reference though. Try removing that. You are also going to want GREATER THAN or equal to for your first date argument, but you only have equal to.

Walter Mootz

Paul Newcome

Dec 21, 2020

Try something along the lines of...

=SUMIFS({Source Sheet Column To Sum}, {Source Sheet Plant Column}, "Plant X", {Source Sheet Date Column}, AND(@cell >= DATE(2020, 10, 01), @cell <=TODAY()))

EDITED to correct a typo in the formula

That solved it, i must not have hit shift on the period to get the greater than symbol. Thank you so much for the help!!

Paul Newcome

Happy to help. 👍️