HELP ON SUMPRODUCT
 

THIS IS MY FORMULA

=SUMPRODUCT((--(INDIRECT($C$5&"!$e$1:$e$5000")36000)*(--(INDIRECT($C$5&"!$D$1:$D$5000")<"Threat found!"))))

e1:e5000 is date
d1:d5000 is for expression, where mostly statement "Threat found!" will be
there.

Now I need to get count of records where there is date in e1:e5000 and
where there is no string expression "Threat found!"

The above formula is counting the title in e1 "Definitions" and in future I
may have some other string between e1:e5000, I need to validate only dates
between e1:e5000. I have put 36000 thinking that I can omit other than date
but "definition" value turns out to be true, so it is taken in to count

Kindly advise and thanks in advance.

HELP ON SUMPRODUCT
 

I think this is what you mean

=SUMPRODUCT(--(ISNUMBER(INDIRECT($C$5&"!$e$1:$e$5000"))),
--(INDIRECT($C$5&"!$e$1:$e$5000")36000),
--(INDIRECT($C$5&"!$D$1:$D$5000")<"Threat found!"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Eddy Stan" wrote in message
...
THIS IS MY FORMULA


=SUMPRODUCT((--(INDIRECT($C$5&"!$e$1:$e$5000")36000)*(--(INDIRECT($C$5&"!$D
$1:$D$5000")<"Threat found!"))))

e1:e5000 is date
d1:d5000 is for expression, where mostly statement "Threat found!" will be
there.

Now I need to get count of records where there is date in e1:e5000 and
where there is no string expression "Threat found!"

The above formula is counting the title in e1 "Definitions" and in future
I
may have some other string between e1:e5000, I need to validate only dates
between e1:e5000. I have put 36000 thinking that I can omit other than
date
but "definition" value turns out to be true, so it is taken in to count

Kindly advise and thanks in advance.

HELP ON SUMPRODUCT
 

I just made a sample and tested this. Worked.
=SUMPRODUCT((INDIRECT(C11&"!A1:A10")38721)*(INDIR ECT(C11&"!B1:B10")<"")*(INDIRECT(C11&"!B1:B10")< "ng!"))

--
Don Guillett
SalesAid Software

"Eddy Stan" wrote in message
...
THIS IS MY FORMULA

=SUMPRODUCT((--(INDIRECT($C$5&"!$e$1:$e$5000")36000)*(--(INDIRECT($C$5&"!$D$1:$D$5000")<"Threat
found!"))))

e1:e5000 is date
d1:d5000 is for expression, where mostly statement "Threat found!" will be
there.

Now I need to get count of records where there is date in e1:e5000 and
where there is no string expression "Threat found!"

The above formula is counting the title in e1 "Definitions" and in future
I
may have some other string between e1:e5000, I need to validate only dates
between e1:e5000. I have put 36000 thinking that I can omit other than
date
but "definition" value turns out to be true, so it is taken in to count

Kindly advise and thanks in advance.

HELP ON SUMPRODUCT
 

Hi Bob & Don,
Thanks both of you..Both are working.

I thought there is no way but you have shown me 2 ways.
I love excel which is getting powerful as we use more & more.



"Eddy Stan" wrote:

THIS IS MY FORMULA

=SUMPRODUCT((--(INDIRECT($C$5&"!$e$1:$e$5000")36000)*(--(INDIRECT($C$5&"!$D$1:$D$5000")<"Threat found!"))))

e1:e5000 is date
d1:d5000 is for expression, where mostly statement "Threat found!" will be
there.

Now I need to get count of records where there is date in e1:e5000 and
where there is no string expression "Threat found!"

The above formula is counting the title in e1 "Definitions" and in future I
may have some other string between e1:e5000, I need to validate only dates
between e1:e5000. I have put 36000 thinking that I can omit other than date
but "definition" value turns out to be true, so it is taken in to count

Kindly advise and thanks in advance.