Formula calculating revenue for Q2

steve50951 · 07/17/19

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

Nic Larsen · 07/17/19

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 · 07/18/19

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 · 07/18/19

Excellent.

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

Thanks

Steve

Paul Newcome · 07/18/19

Happy to help!

Formula calculating revenue for Q2

Comments

Help Article Resources

Categories