Sum cells that contain specific text on rows and columns

Bluew

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!

Genevieve P.

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