Thread: 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.

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" <EddyStan@discussions.microsoft.com> wrote in message
news:94878F97-3596-4483-A24D-2BCD2DA09D84@microsoft.com...
> 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.
>
>

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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Eddy Stan" <EddyStan@discussions.microsoft.com> wrote in message
news:94878F97-3596-4483-A24D-2BCD2DA09D84@microsoft.com...
> 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.
>
>

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.
>
>