Sumproduct With Dates & Text In Same Column
 

I have one column that has my tank numbers and my other column has the
dates entered for when tanks are opened. This formula works fine
counting the matching tanks with the amount of cells that have a date
entered (cells that are not blank):

=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(AA9:AA1611<""))

What I need for my other column is a sumproduct formula that can
determine the difference between a date and a text. The previous
formula only will have a date or have a blank cell. This new column
will have a date or a text.
I have tried the following, but it either counts the text & date or
neither:

=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P1611DATE(2004,1,1)))
=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(YEAR(P9:P1611)=2005))
=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P16111))

The problem I'm sure is with the second part.
Any help would be greatly appreciated.

Sumproduct With Dates & Text In Same Column
 

Not sure that I have got it, but I'll try this

=SUMPRODUCT((ISNUMBER(SEARCH("-J",P9:P16)))+(P9:P16DATE(2004,1,1)))-SUMPROD
UCT((ISNUMBER(SEARCH("-J",P9:P16)))*(P9:P16DATE(2004,1,1)))

--

HTH

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


"Lankchevy" wrote in message
oups.com...
I have one column that has my tank numbers and my other column has the
dates entered for when tanks are opened. This formula works fine
counting the matching tanks with the amount of cells that have a date
entered (cells that are not blank):

=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(AA9:AA1611<""))

What I need for my other column is a sumproduct formula that can
determine the difference between a date and a text. The previous
formula only will have a date or have a blank cell. This new column
will have a date or a text.
I have tried the following, but it either counts the text & date or
neither:

=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P1611DATE(2004,1,1)))
=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(YEAR(P9:P1611)=2005))
=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P16111))

The problem I'm sure is with the second part.
Any help would be greatly appreciated.

Sumproduct With Dates & Text In Same Column
 

That formula dosen't work for my file because I only have tank numbers
in column A (8-121-2-J) and dates, blanks, & text in column P. That did
give me an idea to try the following and that works, but it's messy:

=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P1611DATE(2004,1,1)))-SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P1611="N/A"))-SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P1611="NO"))-SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P1611="Yes"))


I really need something on the end of the first example to determine
the difference between a date and text.

Sumproduct With Dates & Text In Same Column
 

Your examples are confusing me, you first talked about dates and blanks in
the same column but gave examples of two columns, now you show an example
testing for N/A, No or Yes, which is neither date nor blank.

Are you trying to get items that meet the tank number in J where the date in
P is greater than a certain date? If so, where does blank come into it, as
these will fail the tank number test and/or the date greater test? And where
do these new values come into it?

--

HTH

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


"Lankchevy" wrote in message
oups.com...
That formula dosen't work for my file because I only have tank numbers
in column A (8-121-2-J) and dates, blanks, & text in column P. That did
give me an idea to try the following and that works, but it's messy:


=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P1611DATE(2004,1,1)))-SUM
PRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P1611="N/A"))-SUMPRODUCT((ISNU
MBER(SEARCH("-J",A9:A1611)))*(P9:P1611="NO"))-SUMPRODUCT((ISNUMBER(SEARCH("-
J",A9:A1611)))*(P9:P1611="Yes"))


I really need something on the end of the first example to determine
the difference between a date and text.

Sumproduct With Dates & Text In Same Column
 

The first column has the tank numbers 8-12-2-V, 8-121-4-J etc. & the
other column has dates, blanks, & text.
I want to use the sumproduct formula so it will determine the dates
from text and blanks.

Sumproduct With Dates & Text In Same Column
 

I think that makes the blanks irrelevant then

=SUMPRODUCT((ISNUMBER(SEARCH("-J",A9:A1611)))*(P9:P1611DATE(2004,1,1))*(ISN
UMBER(P9:P1611)))

--

HTH

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


"Lankchevy" wrote in message
ups.com...
The first column has the tank numbers 8-12-2-V, 8-121-4-J etc. & the
other column has dates, blanks, & text.
I want to use the sumproduct formula so it will determine the dates
from text and blanks.

Sumproduct With Dates & Text In Same Column
 

Bob,
Thanks for the assistance.
That was the one I needed.
Darrell (Lankchevy)

Sumproduct With Dates & Text In Same Column
 

Whew! Thought we were losing that one Darrell<G

Bob


"Lankchevy" wrote in message
ups.com...
Bob,
Thanks for the assistance.
That was the one I needed.
Darrell (Lankchevy)