Average of lease $ amounts in a certain region

edited 12/09/19 in Formulas and Functions

I would like to see the average lease amount per region.  I am cross sheet referencing. The formula for the average works.  But when I add in the second part (countifs) to make sure a PE GL Checkbox has been checked and to specify a region (I do a cell reference) it doesn't seem to work.  For instance, in 1 region I do not have any leases yet its giving me a dollar amount. This is the formula that I have:


=AVG({Lease Tracker 2018 Lease Rate Offer in $}, COUNTIFS({Lease Tracker 2018 PE GL Checkbox}, 1, {Lease Tracker 2018 Region}, =$Region1))


Any suggestions?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!