Count amount of specific character in a range

Navigation
-1

I have a range of data that looks like this:

I need to count how many Vs and how many Ds you have in the range.

I've tried using SUMIF and COUNTIF

Can anyone help me?

    
asked by anonymous 19.10.2018 / 02:44

1 answer

1

You can use the following formula: 

=SUMPRODUCT(LEN(A1:A7)-LEN(SUBSTITUTE(A1:A7;"V";"")))

Where:

  • A1:A7 = Range
  • "V" = Desired character
  

How does it work?

     
  • LEN(SUBSTITUTE(A1:A7;"V";"")) removes all occurrences of the "V" letter of the text and counts the number of characters remaining
    •   
  • Calculating the difference between LEN(A1:A7) (original text) and LEN(SUBSTITUTE(A1:A7;"V";"")) (text without the "V" s) you can find out how many Vs have been removed from the text and thus know how many times the character appears.   
  • SUMPRODUCT adds the numbers in the list
    •   

Font

    
19.10.2018 / 07:03
How to reduce this PHP / MYSQL function by adding data in multiple columns? [closed] function with error when return alert