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.

Darrell,

You can probably use a Pivot Table. Have it summarize the data in column A and the column in which the date is entered. Then you can format it so that it hides the blanks and subtotals so all you see is tank # (1-1-2-f. etc..) the date opened and the grand total. Use the Pivot Table wizard and it takes about just a few seconds.


Cheers,

Steve

if you want to count the number of cells with J's or V's in that range, you
could use:

=countif(a1:a4,"*J*")

1-1-2-v
1-j-j-j
j-j-j-j
j-j-j-j

would return 3 (three cells with at least a single J in them).

If your data could have multiple J's and you want to count all of them:

=SUM((LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"j","")))/LEN("j"))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

Ps. That formula returned 11 with my test data.

[email protected] 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.

--

Dave Peterson