Thread: Formula text count

I Have a list in a column that has a lot of tank numbers on a ship
listed as the following:

1-1-2-V
1-1-3-V
1-1-2-F
1-1-2-F
1-1-2-J
1-1-2-F

In another column, I have a list to identify whether the tank has been
opened noted with a date if it has and blank if it hasn't. I'm trying
to count the amount of V tanks that have been opened, J tanks that have
been opened and so on. I need an if, ifcount, or vlookup formula that
can do this. Any assistance would be greatly appreciated.

Hi,

Let's suppose that the tank numbers are in A2:A101 and dates (or blanks) are
in B2:B101. In some other cell, say D2, enter the following formula,

= SUMPRODUCT((RIGHT($A$2:$A$101,1)="V")*(ISNUMBER($B$2:$B$101)))

Change "V" to "J" etc for other tank labels.

You can simplify this by putting the tank labels in helper cells. For
example,
enter V in C2, J in C3, F in C4, etc., and enter the following formula in D2
(and drag the formula down D3, D4, .....

=SUMPRODUCT((RIGHT($A$2:$A$10,1)=C2)*(ISNUMBER($B$2:$B$10)))

Regards,
B. R. Ramachandran

"darrelll@earl-ind.com" wrote:

> I Have a list in a column that has a lot of tank numbers on a ship
> listed as the following:
>
> 1-1-2-V
> 1-1-3-V
> 1-1-2-F
> 1-1-2-F
> 1-1-2-J
> 1-1-2-F
>
> In another column, I have a list to identify whether the tank has been
> opened noted with a date if it has and blank if it hasn't. I'm trying
> to count the amount of V tanks that have been opened, J tanks that have
> been opened and so on. I need an if, ifcount, or vlookup formula that
> can do this. Any assistance would be greatly appreciated.
>
>