Could someone help me with this formula to calculate the overall rating?
In the formula, I'm trying to SUM criteria 1, 2 & 3, but the numeric value changes based on the text in the fields. For example, IF criteria 1="SOMETIMES" give a value of 3, IF criteria 1="OFTEN" give a value of 4, IF criteria 1="ALWAYS" give a 5. This would be the same for criteria 2 and 3.
So based on the above rating in each category, the overall rating should be 14, but I'm obviously doing something wrong :-( since this should be 15:
Appreciate any assistance.
Best Answer
-
With the columns being contiguous, consider using a COUNTIFs approach. This will count how many of each type response you have and multiply by the appropriate amount. If, for some reason, an error occurs then the result of the cell will be zero,
=IFERROR(5 * COUNTIFS([Stellar Criteria 1 - Express ideas]@row:[Stellar Criteria 3 - Clearly explains]@row, "Always") + 4 * COUNTIFS([Stellar Criteria 1 - Express ideas]@row:[Stellar Criteria 3 - Clearly explains]@row, "Often")+ 3 * COUNTIFS([Stellar Criteria 1 - Express ideas]@row:[Stellar Criteria 3 - Clearly explains]@row, "Sometimes"),0)
Would this work for you?
Answers
-
With the columns being contiguous, consider using a COUNTIFs approach. This will count how many of each type response you have and multiply by the appropriate amount. If, for some reason, an error occurs then the result of the cell will be zero,
=IFERROR(5 * COUNTIFS([Stellar Criteria 1 - Express ideas]@row:[Stellar Criteria 3 - Clearly explains]@row, "Always") + 4 * COUNTIFS([Stellar Criteria 1 - Express ideas]@row:[Stellar Criteria 3 - Clearly explains]@row, "Often")+ 3 * COUNTIFS([Stellar Criteria 1 - Express ideas]@row:[Stellar Criteria 3 - Clearly explains]@row, "Sometimes"),0)
Would this work for you?
-
Oh, wow! Didn't consider that, but yes, this would and does work :-) Thank you!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!