Row Count With Matching Cell Criteria

[Lankchevy]

I need a formula that will count the number of rows (or times) in which
a cell has a date and the other has a certain tank number list. For
example the list in column A has tank numbers like the following:
6-125-4-V
6-120-5-J
8-14-2-V
6-12-3-F
8-12-2-J
In column D, most of the cells are blank until the tank is opened and a
date is entered.
I want to count the number of J tanks that have a date entered, the
number of V tanks that have a date entered, and etc. The closest I have
gotten is with a sumproduct formula but I can't figure out how to match
the certain tank letters with each date entered.
I tried some of this, but none is working:
=SUMPRODUCT((RIGHT(A9:A1611,2)="-J")*(M9:M1611<>""))
=SUMPRODUCT((A9:A1611="-J")*(M9:M1611<>""))
=SUMPRODUCT(ISTEXT(FIND("J",A9:A1611))*(M9:M1611<>""))
=SUMPRODUCT(COUNTIF(A9:A1611,"*-J*")*(M9:M1611<>""))
Any assistance would be greatly appreciated.

 

[Ragdyer]

You mention dates in Column D in your question, but use Column M in your
formula examples.

Let's use D for dates.

In E9 to E11 enter:
V, J, F

Then in F9, enter this formula:

=SUMPRODUCT((RIGHT($A$9:$A$1611)=E9)*(ISNUMBER($D$9:$D$1611)))

And copy down to F11.

You should now have your number of open tanks next to the tank ID.

 

[Ragdyer]

Actually, looking more closely at *your* formulas, some should have worked.

This leads me to think that perhaps your tank ID data is not as it appears.
Perhaps you might have "hidden" characters or spaces.

If my first suggestion doesn't work, try this one:

=SUMPRODUCT((RIGHT(TRIM($A$9:$A$1611))=E9)*(ISNUMBER($D$9:$D$1611)))

If the above formula doesn't work either, try manually entering some tank
ID's, where you *know* that there is no hidden characters.

 

[Bob Phillips]

The first one should do it

=SUMPRODUCT((RIGHT(A9:A1611,2)="-J")*(M9:M1611<>""))

what was wrong with that one.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Lankchevy]

I'm sorry about the column D that I mentioned. I have my dates in
column M. I tried what you suggested, but the way I have it setup, it's
not working. The SUMPRODUCT(('Tank Chart'!A9:A1611="-J")*('Tank
Chart'!M9:M1611<>"")) worked if I took out the tank number and used the
word "test1" instead of "J" (putting test in place of the tank#). The
problem is it's not picking out the "J" from the tank number. When I
use COUNTIF(A9:A1611,"*-J*") to count the total number of J tanks, it
works, but it's not working for the sumproduct formula.

 

[Peo Sjoblom]

=SUMPRODUCT(--(ISNUMBER(SEARCH("-J",'Tank Chart'!A9:A1611))),--('Tank
Chart'!M9:M1611<>""))

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Lankchevy]

Peo,
It works like a champ.
Thanks everyone for the help.
Darrell (Lankchevy)