getting the sum of letters

[andrewb90]

Hi all! I'm sure this is fairly simple but, how do I add up the total number of times column D has a value of 'B'? The only other thing that may complicate this is some rows column D is merged with other cells so I want that excluded, and I only want to count the cells that are currently visible. Any hidden cells need not be counted.

Thanks,

Andrew

 

[scottiedawg4]

=countif($d:$d,"b")

 

[andrewb90]

Tried that but it doesn't exclude the hidden rows. I need to make sure those are excluded.

 

[barry houdini]

You can use this formula to exclude hidden rows too. Assumes a range D2:D100

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2,ROW(D2:D100)-ROW(D2),0)),(D2:D100="b")+0)

 

[scottiedawg4]

You'll have to use the subtotal function as well.

 

[andrewb90]

Still encountering the same thing. The hidden cells are still getting counted. Scottie what's the subtotal function?

 

[T. Valko]

Still encountering the same thing. The hidden cells are still getting counted. Scottie what's the subtotal function?

Try this tweak of Barry's formula.

=SUMPRODUCT(SUBTOTAL(103,OFFSET(D2,ROW(D2:D100)-ROW(D2),0)),--(D2:D100="b"))

 

[andrewb90]

Fantastic! Thanks a bunch!

 

[Jasa P]

Sorry for disturbing you. I have a similar problem, but just more complicated.

I have a combination of
20.10.10x5.15.25/
in cell A1

The problem are:
a. The result for combination before "x" should be the amount of dots (.) + 1, so the result is 3
b. The result for combination between "x" and "/" should be the sum of the numbers, so the result is 45

Then, the results from a and b must be multiplied, so the final result is 135 in cell B1

I'm using Excel 2007 and Windows XP

I really need some help, please. Thank you so much

Best Regards

Jasa

 

[T. Valko]

Fantastic! Thanks a bunch!

You're welcome!