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

 

[T. Valko]

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

See your other thread.

 

[Jasa P]

Do u know the right tag of this problem? 'cause I'm confused about the right issue for this formula. Is this the correct one "obtain the multiplication of the sum of dots and numbers from letters"? Thank you.

 

[andrewb90]

How would I use that formula to count anything that is not hidden in a column? basically the total number of rows that I can see.

thanks again,

Andrew

 

[T. Valko]

Do u know the right tag of this problem? 'cause I'm confused about the right issue for this formula. Is this the correct one "obtain the multiplication of the sum of dots and numbers from letters"? Thank you.

This is a hard one to classify.

I think I might use something like:

Sum the numbers in a cell that are delimited by a dot.

 

[T. Valko]

How would I use that formula to count anything that is not hidden in a column? basically the total number of rows that I can see.

thanks again,

Andrew

If I understand, try this...

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

 

[Jasa P]

I'll try your advice and take a look about "Sum the numbers in a cell that are delimited by a dot". Thank you so much for your help. I really appreciated that, thank u

 

[T. Valko]

I'll try your advice and take a look about "Sum the numbers in a cell that are delimited by a dot". Thank you so much for your help. I really appreciated that, thank u

You're welcome!

 

[andrewb90]

If I understand, try this...

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

That does work correctly, however, I turns out that it was not the thing I needed.
using your original solution, is it possible to have 2 variables such as 'b' or 'c'?
I have a subscription list, but certain rows just have delivery instructions and not an address so I don't want to count instruction lines in the total.

Thanks!
Andrew

 

[T. Valko]

That does work correctly, however, I turns out that it was not the thing I needed.
using your original solution, is it possible to have 2 variables such as 'b' or 'c'?
I have a subscription list, but certain rows just have delivery instructions and not an address so I don't want to count instruction lines in the total.

Thanks!
Andrew

So, you only want to count visible cells that contain either B or C?

Try this:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(D2,ROW(D2:D100)-ROW(D2),0)),--(ISNUMBER(MATCH(D2:D100,{"b","c"},0))))

 

[andrewb90]

Awesome! Thanks so much!