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.

Formula text count
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=482308

Formula text count
 

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.

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