How to create an average using SUMIF as the denominator
I am trying to create an array of construction costs then extract the square foot costs. The issue I have is not all projects have the scope of work. For instance, I have new lighting on some but not all projects. So I want to create an average using the the total costs divided by just the cells that have square footage associated with the cost.
=((SUM(P11:BY11)/(SUMIF($P$2:$BY$2,P11:BY11>0)))) Where row 11 is the dollar value (when used) and row 2 is the square footage of the project
Answers
-
-
Can you please include the column headers so I can see what your formula is referring to?
Second question is are you receiving an error or is something not working with your formula?
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!