SUM Formula Does not work with Quarter Formula

Andrea Westrich

This is my test sheet. I have the following formula in Quarter column:

=IFERROR(IF(MONTH([Contract Acceptance Date]@row) <= 3, "1", IF(MONTH([Contract Acceptance Date]@row) <= 6, "2", IF(MONTH([Contract Acceptance Date]@row) <= 9, "3", "4"))), "")

The above works fine.

I want to summarize sales by the 3rd quarter, so I created a summary field with the following formula: =SUMIF([Quarter of Contract for Formula]:[Quarter of Contract for Formula], "3", [Sales Price]:[Sales Price])

With the above scenario, the sumif formula returns a 0 on the above data. If I remove the Quarter formula and put in the number of each quarter by hand, the Sumif formula works.

Using IFERROR, also produces a 0 sum.

=IFERROR(SUMIF([Quarter of Contract for Formula]:[Quarter of Contract for Formula], "3", [Sales Price]:[Sales Price]), "")

Any ideas on this?

Sameer Karkhanis

@Andrea Westrich This is because the values you are getting from your formula for the quarter column are in text format due to the quotes that you have mentioned around the numbers. Just remove the quotes and you will have number values as needed for your summary formula. Just use the below in your quarter column formula,

=IFERROR(IF(MONTH([Contract Acceptance Date]@row) <= 3, 1, 
          IF(MONTH([Contract Acceptance Date]@row) <= 6, 2, 
           IF(MONTH([Contract Acceptance Date]@row) <= 9, 3, 4)
           )
          ),
       "")

Sameer Karkhanis

@Andrea Westrich This is because the values you are getting from your formula for the quarter column are in text format due to the quotes that you have mentioned around the numbers. Just remove the quotes and you will have number values as needed for your summary formula. Just use the below in your quarter column formula,

=IFERROR(IF(MONTH([Contract Acceptance Date]@row) <= 3, 1, 
          IF(MONTH([Contract Acceptance Date]@row) <= 6, 2, 
           IF(MONTH([Contract Acceptance Date]@row) <= 9, 3, 4)
           )
          ),
       "")

Andrea Westrich

@SK that fixed it!

Thank you so much!!