Summing a column if a date falls within a quarter.

GabyC

Hello,

Could someone help me with a formula that states the following: sum the square footage if Construction Start date is within the Quarter and the Status is one of these: Complete, Construction, Executed LOI, Priority. I'm thinking I may have to add 2 hidden date columns to clarify the different quarters.

Formula page:

Data page:

sharkasits

Trying to do it all in one formula will get messy/long. I agree that having two hidden date columns would help, but you can at least set them up with formulas so you don't need to update them as rows get added.

Start Date =DATE(VALUE(RIGHT(Quarter@row, 4)), VALUE(MID(Quarter@row, 2, 1)) * 3 - 2, 1)

End Date =DATE(VALUE(RIGHT(Quarter@row, 4)), VALUE(MID(Quarter@row, 2, 1)) * 3 + 1, 1) - 1

Square Footage = =SUMIFS({Square Footage}, {Start Date}, >=[Start Date]@row, {Start Date}, <=[End Date]@row, {Status}, OR(HAS(@cell, "Complete"), HAS(@cell, "Construction"), HAS(@cell, "Executed LOI"), HAS(@cell, "Priority")))

Paul Newcome

You could either put the quarter start and end dates in their own columns on the formula sheet, or you can hard code them into formulas.

(edited to work in the statuses)

=SUMIFS({Data Sheet FT2 Column}, {Data Sheet Date Column}, AND(@cell>= [Quarter Start]@row, @cell<= [Quarter End]@row), {Data Sheet Status Column}, OR(@cell = "Complete", @cell = "2nd Status", @cell = "3rd Status"))

=SUMIFS({Data Sheet FT2 Column}, {Data Sheet Date Column}, AND(@cell>= DATE(yyyy, mm, dd), @cell<= DATE(yyyy, mm, dd)), {Data Sheet Status Column}, OR(@cell = "Complete", @cell = "2nd Status", @cell = "3rd Status"))

GabyC

I entered as a column formula and I received a few invalid values.

Paul Newcome

Which formula gave you the error?

GabyC

End Date =DATE(VALUE(RIGHT(Quarter@row, 4)), VALUE(MID(Quarter@row, 2, 1)) * 3 + 1, 1) - 1

sharkasits

@GabyC Sorry, forgot to account for year end funkiness with dates...

End Date =IF(VALUE(MID(Quarter@row, 2, 1)) = 4, DATE(VALUE(RIGHT(Quarter@row, 4)) + 1, 1, 1) - 1, DATE(VALUE(RIGHT(Quarter@row, 4)), VALUE(MID(Quarter@row, 2, 1)) * 3 + 1, 1) - 1)

GabyC

@sharkasits that worked, thank you! I am also getting an error on this formula.

=SUMIFS({Square Footage}, {Start Date}, >=[Start Date]@row, {Start Date}, <=[End Date]@row, {Status}, OR(HAS(@cell, "Complete"), HAS(@cell, "Construction"), HAS(@cell, "Executed LOI"), HAS(@cell, "Priority")))

sharkasits

@GabyC did you update the {references} to your data sheet? If yes, what error are you getting?

GabyC

@sharkasits it worked when i updated the references. thank you!