Formula calculating revenue for Q2

I am trying to calculate revenue for Q2 only for a given property

Here's what i had in the cell

=SUMIFS({Garden Grove Bill Rate}, {Garden Grove Inv Date}, YEAR(@cell) = YEAR(TODAY()), {Garden Grove Inv Date}, MONTH(@cell) > 3).

But that formula doesnt work now that we are in july. as it calculates july revenue as well.

So this is what i changed it to

=SUMIFS({Creekwood Apartments Bill Rate}, {Creekwood Apartments Inv Date}, YEAR(@cell) = YEAR(TODAY()), {Creekwood Apartments Inv Date}, MONTH(@cell) > 3, {Creekwood Apartments Inv Date}, MONTH(@cell) < 7)

Just wandering if there is an easier way, other than adding another @row reference. Can i use a date range with an @row reference that would express months 3-6?

thanks,

SGF

Find more posts tagged with

Formulas

Comments

Nic Larsen

Try this:

=SUMIFS({Garden Grove Bill Rate}, {Garden Grove Inv Date}, >=DATE(2019, 4, 1), {Garden Grove Inv Date}, <=DATE(2019, 6, 30))

Paul Newcome

When referencing the same range twice within a SUMIFS, you can use the AND function to help save yourself from having to duplicate the range.

=SUMIFS({Creekwood Apartments Bill Rate}, {Creekwood Apartments Inv Date}, AND(YEAR(@cell) = YEAR(TODAY()), MONTH(@cell) > 3, MONTH(@cell) < 7))

steve50951

Excellent.

Thanks guys! Works great! and saves me a few key strokes.

Thanks

Steve

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…