adding multiple cell contents

[Bobbie]

The cells in column H contain multiple contents (single or
pairs of letters - ys, a, p, fg, etc.) I have set up
another column with the cell choices where I would like
the totals of each letter/pairing in column H to go.

So, cell H5 could contain fg, a, ys. Cell H6 could
contain ys, p, fg, fg. In the appropriate cell, how would
I total all of the fgs in column H? (And all the other
letters/pairings in their appropriate cells)

I can't figure out a formula. COUNTIF, sum, and other
listed choices only total the number of cells, not
contents.

 

[Peo Sjoblom]

One way, although I recommend that you change the layout

=SUMPRODUCT(LEN(H2:H1000)-LEN(SUBSTITUTE(H2:H1000,"ys","")))/LEN("ys")

will count "ys", replace the "ys" in the formula with a cell where you can
type the criteria

 

[Bobbie]

Thank you, thank you, it works!

-----Original Message-----
One way, although I recommend that you change the layout

=SUMPRODUCT(LEN(H2:H1000)-LEN(SUBSTITUTE (H2:H1000,"ys","")))/LEN("ys")

will count "ys", replace the "ys" in the formula with a cell where you can
type the criteria

--

Regards,

Peo Sjoblom





.