Function to assign value/number to symbols and sum all of them.

Question

Level 1

4 points

Function to assign value/number to symbols and sum all of them.

Hi everyone,

In a table I have three rows where I have to record every employee’s shift over the month. The columns represents every single day of the month. Every employee works on a two-week-shift basis. Inside those working days, there could be different symbols. Each one of them represents a number of worked hour and I want to verify how many hours they work every month.

I.e.: ↑ is 9, ◉ is 12, ⌽ is 6 and so on.

I know it could be easier to put a number instead of a symbol, but I would not be here asking for help.

So, which formula should I use in a single cell? At the right of the table I would like to put a cell that counts the total worked hours for every employee.

Posted on Jan 31, 2021 6:56 AM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

59,951 points

Jan 31, 2021 5:50 PM in response to mfigari

The formula shown below the table is entered in cell AG4, and filled down to AG6. As entered (with COUNTIF parts for only the three symbol to number correspondence) the totls will be incorrect.

You will need a copy of the num *COUNTIF(B4:AF4,"symbol") calculation for each symbol used. When entering the formuls, use an asterisk (shift-8) where the styled formula shows a multiplication sign. Numbers will immediately replace the multiplication operator ( * ) with the multiplication sign ( × ), but will not recognize the multiplication sign if it is typed or entered directly.

Regards,

Barry

Reply