excel - Count Occurrence of a Character in a String on Multiple Rows Sorted by Keyword [vba] -

vba newbie. encountered excel problem not address formulas. here sample data:

----------------------- |   |       |  b*   | ----------------------- | 1 |  text1  |  3001 | ----------------------- | 2 |  text2  |  0231 | ----------------------- | 3 |  text1  |  1003 | ----------------------- | 4 |  text3  |  0012 | ----------------------- 

achieve f(a1:a4, "text1", b1:b4, "3") = 2.

i.e. want count cumulative occurrence of character "3" in column b every row contains "text1". please note cells in column b of type text vs int. if there way achieve without vba, welcome too.

here have far:

function countcharinstr(filterstr string, filterrange range, searchstr string,    strrange range) integer      dim idx integer, isfound boolean     countcharinstr = 0     isfound = false      idx = 1 filterrange.rows.count         isfound = true         if filterrange(idx) <> filterstr             isfound = false             exit         end if          if isfound             countcharinstr = len(strrange(idx)) - len(replace(strrange(idx), searchstr, "")) + countcharinstr             exit         end if     next idx  end function 

function returns 0, initial value, instead of 2. know if there simple way step through user defined function debugging purposes.

many thanks!

you can achieve directly on worksheet:

     =sum(if(a1:a4="text1",len(b1:b4)-len(substitute(b1:b4,"3","")),0)) 

note array formula: have press ctrl+shift+enter (in formula bar) instead of enter when editing.

always better avoid vba if possible.

let me know if need explanation on how works.