Hello all,
I am trying to count the number of SEV 1 tickets, gouped by time 0-4hrs, 4-8
hrs. Previous user says it worked for him. This should give a count of the
number of SEV 1 tickets that were created less than 4 hours from now.

Cell B16 contains the number 1
Named Ranged value, open tab column C = Severity
open tab column D contains the date and time ticket created ie. 10/5/2006
14:10

Formula previous user had now shows #VALUE, but if I F2 in the cell then hit
enter, the cell results in the cell goes blank (nothing)
=SUM(IF(NOW()-Open!D1:D977<0.166,IF(Open!C1:C977=$B$16,1)))

I've been reading this newgroup and came up with this one, but still get no
luck. I get #NUM.

=SUMPRODUCT((Severity=$B$16)*(NOW()-Open!D:D0.1666))

Once this seems to work, I will put a reference to a static date and time
instead of NOW().

Any assistance you can provide would be greatly appreciated.

Thank you,

Based on Roger Govier's response earlier today on another post,

http://msdn.microsoft.com/newsgroups...a-090bb7563091

I tried this. Also get #Value error

=SUMPRODUCT(--(NOW()-Open!$D$1:$D$655350.16666),--(Open!$C$1:$C$65535=$B$16))



"David" wrote:

Hello all,
I am trying to count the number of SEV 1 tickets, gouped by time 0-4hrs, 4-8
hrs. Previous user says it worked for him. This should give a count of the
number of SEV 1 tickets that were created less than 4 hours from now.

Cell B16 contains the number 1
Named Ranged value, open tab column C = Severity
open tab column D contains the date and time ticket created ie. 10/5/2006
14:10

Formula previous user had now shows #VALUE, but if I F2 in the cell then hit
enter, the cell results in the cell goes blank (nothing)
=SUM(IF(NOW()-Open!D1:D977<0.166,IF(Open!C1:C977=$B$16,1)))

I've been reading this newgroup and came up with this one, but still get no
luck. I get #NUM.

=SUMPRODUCT((Severity=$B$16)*(NOW()-Open!D:D0.1666))

Once this seems to work, I will put a reference to a static date and time
instead of NOW().

Any assistance you can provide would be greatly appreciated.

Thank you,

Hi David

Try inserting brackets around Open!$D$1:$D$65535
Works fine for me.

=SUMPRODUCT(--(NOW()-(Open!$D$1:$D$65535)0.16666),
--(Open!$C$1:$C$65535=$B$16))

--
Regards

Roger Govier


"David" wrote in message
...
Based on Roger Govier's response earlier today on another post,

http://msdn.microsoft.com/newsgroups...a-090bb7563091

I tried this. Also get #Value error

=SUMPRODUCT(--(NOW()-Open!$D$1:$D$655350.16666),--(Open!$C$1:$C$65535=$B$16))



"David" wrote:

Hello all,
I am trying to count the number of SEV 1 tickets, gouped by time
0-4hrs, 4-8
hrs. Previous user says it worked for him. This should give a count
of the
number of SEV 1 tickets that were created less than 4 hours from now.

Cell B16 contains the number 1
Named Ranged value, open tab column C = Severity
open tab column D contains the date and time ticket created ie.
10/5/2006
14:10

Formula previous user had now shows #VALUE, but if I F2 in the cell
then hit
enter, the cell results in the cell goes blank (nothing)
=SUM(IF(NOW()-Open!D1:D977<0.166,IF(Open!C1:C977=$B$16,1)))

I've been reading this newgroup and came up with this one, but still
get no
luck. I get #NUM.

=SUMPRODUCT((Severity=$B$16)*(NOW()-Open!D:D0.1666))

Once this seems to work, I will put a reference to a static date and
time
instead of NOW().

Any assistance you can provide would be greatly appreciated.

Thank you,

Roger,
Thank you for your response. I'm a bit new to all of this. I had column
headings on row 1. Modified to start at row 2, and it works great.

"Roger Govier" wrote:

Hi David

Try inserting brackets around Open!$D$1:$D$65535
Works fine for me.

=SUMPRODUCT(--(NOW()-(Open!$D$1:$D$65535)0.16666),
--(Open!$C$1:$C$65535=$B$16))

--
Regards

Roger Govier


"David" wrote in message
...
Based on Roger Govier's response earlier today on another post,

http://msdn.microsoft.com/newsgroups...a-090bb7563091

I tried this. Also get #Value error

=SUMPRODUCT(--(NOW()-Open!$D$1:$D$655350.16666),--(Open!$C$1:$C$65535=$B$16))



"David" wrote:

Hello all,
I am trying to count the number of SEV 1 tickets, gouped by time
0-4hrs, 4-8
hrs. Previous user says it worked for him. This should give a count
of the
number of SEV 1 tickets that were created less than 4 hours from now.

Cell B16 contains the number 1
Named Ranged value, open tab column C = Severity
open tab column D contains the date and time ticket created ie.
10/5/2006
14:10

Formula previous user had now shows #VALUE, but if I F2 in the cell
then hit
enter, the cell results in the cell goes blank (nothing)
=SUM(IF(NOW()-Open!D1:D977<0.166,IF(Open!C1:C977=$B$16,1)))

I've been reading this newgroup and came up with this one, but still
get no
luck. I get #NUM.

=SUMPRODUCT((Severity=$B$16)*(NOW()-Open!D:D0.1666))

Once this seems to work, I will put a reference to a static date and
time
instead of NOW().

Any assistance you can provide would be greatly appreciated.

Thank you,