Count specific character amount in a range

Asked

Viewed 60 times

-1

I have a data range similar to that:

enter image description here

I need to count how many V’s and D’s are in between.

I’ve tried using SUMIF and COUNTIF

Can someone help me?

accountant google-sheets

1 answer

1


You can use the following formula: 

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

Where:

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

How it works?

  • LEN(SUBSTITUTE(A1:A7;"V";"")) removes all occurrences of the letter "V" from 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 "V"s) it is possible to find out how many Vs have been removed from the text and so know how many times the character appears.
  • SUMPRODUCT sum the numbers in the list

Source

  • Thank you very much. Saved! Haha

Browser other questions tagged accountant google-sheets

You are not signed in. Login or sign up in order to post.