You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

User profile for user: thanpisit19
thanpisit19 Author
User level: Level 1
25 points

Calculate sum from text

Hi,


I am trying to sum things automatically. Here's the situation.


I want the following letters to have the following value.

A = 0, L = 1, P and E = 2,


I want each cell with A,L,P or E to have value of 2. Meaning:

If the cell is filled with "A" that cell will have value of 0/2.

If the cell is filled with "L" that cell will have value of 1/2.

If the cell is filled with "P or E" that cell will have value of 2/2.

Cells that filled with other letters will have no contribution to the sum.


Please see the screenshot below for reference.




Thanks,

Thanpisit



MacBook Pro 13″, macOS 13.5

Posted on Nov 28, 2024 10:22 PM

Reply
Question marked as Top-ranking reply
User profile for user: Camelot
Camelot
User level: Level 9
55,341 points

Posted on Nov 29, 2024 3:59 PM

This isn't hard.


I broke it down into two stages to demonstrate the method.


The first part counts the number of P, L, or E values and adds the score. That looks like:


COUNTIF(A2:O2,"=L")+(COUNTIF(A2:O2,"=P")×2)+(COUNTIF(A2:O2,"=E")×2)


This is a basic sum of the number of "L" cells in the range A2:O2, plus the number of cells containing P or E multiplied by 2. This gives you the score.


The second part calculates the number of APLE using another COUNTIF() function:


=COUNTIF($A2:$O2,REGEX("^A$|^P$|^L$|^E$",FALSE))×2


This uses a regular expression "^A$|^P$|^L$|^E$" to match any one of those characters as a single character in any cell. The FALSE at the end indicates case-insensitive. Since you want two points per cell, we simply multiple this by 2.


Now we have two formulas that determine the values you want to display. To show them as a fraction, I use CONCAT():


=CONCAT(COUNTIF(A2:O2,"=L")+(COUNTIF(A2:O2,"=P")×2) + (COUNTIF(A2:O2,"=E")×2),"/",COUNTIF($A2:$O2,REGEX("^A$|^P$|^L$|^E$",FALSE))×2)


This simply combines the first function (sum of P, L, E cells), a / character, and the second function (count of A/P/L/E cells)

View in context
2 replies
Question marked as Top-ranking reply
User profile for user: Camelot
Camelot
User level: Level 9
55,341 points

Nov 29, 2024 3:59 PM in response to thanpisit19

This isn't hard.


I broke it down into two stages to demonstrate the method.


The first part counts the number of P, L, or E values and adds the score. That looks like:


COUNTIF(A2:O2,"=L")+(COUNTIF(A2:O2,"=P")×2)+(COUNTIF(A2:O2,"=E")×2)


This is a basic sum of the number of "L" cells in the range A2:O2, plus the number of cells containing P or E multiplied by 2. This gives you the score.


The second part calculates the number of APLE using another COUNTIF() function:


=COUNTIF($A2:$O2,REGEX("^A$|^P$|^L$|^E$",FALSE))×2


This uses a regular expression "^A$|^P$|^L$|^E$" to match any one of those characters as a single character in any cell. The FALSE at the end indicates case-insensitive. Since you want two points per cell, we simply multiple this by 2.


Now we have two formulas that determine the values you want to display. To show them as a fraction, I use CONCAT():


=CONCAT(COUNTIF(A2:O2,"=L")+(COUNTIF(A2:O2,"=P")×2) + (COUNTIF(A2:O2,"=E")×2),"/",COUNTIF($A2:$O2,REGEX("^A$|^P$|^L$|^E$",FALSE))×2)


This simply combines the first function (sum of P, L, E cells), a / character, and the second function (count of A/P/L/E cells)

Reply

Calculate sum from text

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up