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.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!