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
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 " 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) |