Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula text count | Excel Discussion (Misc queries) | |||
Count If formula | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) |