Sum cells that contain specific text on rows and columns
Hi Smartsheet Community!
I've been searching for solutions to my problem for few days not but haven't been able to figure it out so maybe you can help me please 🙏
I have a spreadsheet with the following structure:
So, every employee is evaluated by its peer and manager, who are each given an individual feedback for every item. The feedback is in written, in text. Every cell that contains text values 2.5 points; if no text is written in cells then there are no points given. And I want in the cells with total score to add up the sum for each feedback, but separated for peer and manager. Is there a way that Excel can add 2.5 to every cell that contains text but only on columns with "feedback employee"? So let's say that if every cell with employee feedback are written, then the score for feedback employee should be 15.
I tried this formula, on the original spreadsheet, but it doesn't work: =IF(L2:S2="Feedback peer";SUMPRODUCT(ISTEXT(L3:S3)*2,5);"error")
I'd appreciate any help!
Thanks a lot!
Answers

Hi @Bluew
Do you have a screen capture of how you have this set up in Smartsheet?
We can definitely use multiple IF statements to assign a value of 2.5 to each cell that you want to check. For example:
=IF([Feedback Item 1]@row <> "", 2.5, 0) + IF([Feedback Item 2]@row <> "", 2.5, 0)
However it would be helpful to know how your sheet is structured in the Smartsheet application to identify column names etc.
Cheers,
Genevieve
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!