1

After researching for a solution on how to count text in a cell I've run into an issue and I'm unsure what's causing incorrect results or if I am not fully using this correctly. In my research I've read and tested:

Which suggest using COUNTIF and I can reference Sheet 1 named foo from Sheet 2 named bar using:

=COUNTIF(foo!$A$1,"$")

However, if the contents of Sheet 1 A:1 contain the text:

$1.00
$1.32
$13.98
$42.88

I'm looking for the count of times the $ is present in a cell but when I use =COUNTIF(foo!$A$1,"$") it just returns 1. When I click the help section on Learn more about COUNTIF I see where it shows COUNTA but now COUNTA only returns 2?

When I look up =QUERY it says for the range but I'm not looking for the range I'm looking for the total count of $ in a cell.

In Google Spreadsheets how can I return the correct count of $ in from Sheet 1 named foo from cell A:1 to Sheet 2 named bar cell A:1 so that if A:2 has 10 instances of $ it will show 10 in Sheet named bar A:2?

DᴀʀᴛʜVᴀᴅᴇʀ
  • 7,681
  • 17
  • 73
  • 127

1 Answers1

2

Bar!A1: 

=ARRAYFORMULA(LEN(foo!A1:A5)-LEN(SUBSTITUTE(foo!A1:A5,"$",)))

It's common practice to just subtract length of substituted cell from the actual cell length to get count of the substitution string.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • That's close what I needed was just `A1` not the range from `A1:A5`. Also can you expand on what is happening? Why did you choose `ARRAYFORMULA()` and then use it with `LEN()`? – DᴀʀᴛʜVᴀᴅᴇʀ Apr 24 '18 at 21:16