CODEWITHSUNDEEP
google-sheetscount
GʀᴜᴍᴘʏCᴀᴛ
GʀᴜᴍᴘʏCᴀᴛ

Reputation: 8918

How do you count text from a cell in another sheet with Google Spreadsheets?

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?

Upvotes: 1

Views: 935

Answers (1)

TheMaster
TheMaster

Reputation: 50462

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.

Upvotes: 2

Related Questions