IF function working with LEN and Substitute

[cd109]

Hi

I have a formula that looks up the number of occurances of a single character in a column, some cells contain more than one character.

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"A","")))

I want to add an IF function to this so that it will only carry out the above formula IF a certain word appears in another column of that same row? But then I don't want this to cancel out and return a 0 if the word does not appear in a row.

Row 1 Row 2
Red .........A
Blue..........A
Red .........A
Red......... A

For example with the above if the certain word was "red" it will only return a value of 3 as the 2nd column should not count.

Any help would be appreciated

Thanks

[JBeaucaire]

Try something like this:

=SUMPRODUCT(--($A$1:$A$4 = "Red"), --($B$1:$B$4 = "A"))

[DGagnon]

assuming column A has your words, B has your count of letters you can use this

=SUMPRODUCT(ISNUMBER(FIND("Red",A1:A4))*B1:B4)

without B you could use this:

=SUMPRODUCT(ISNUMBER(FIND("Red",A1:A4))*SUM(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"A",""))))

[daddylonglegs]

Try

=SUMPRODUCT((A1:A4="Red")+0,LEN(B1:B4)-LEN(SUBSTITUTE(B1:B4,"A","")))

[JBeaucaire]

Or a correction on mine:

=SUMPRODUCT(--($A$1:$A$4="red"),--(ISNUMBER(SEARCH("A", $B$1:$B$4))))

[DGagnon]

a note on the solution i provided, i was under the impression that both the word and letter being searched for were in the same cell.

[cd109]

Thanks all - I'm going to try some of these this afternoon and let you know - can't get on the spreadsheet at the moment - its a shared one at work!

Really appreciate your help!