Sumproduct help
 

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,

Sumproduct help
 

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,

Sumproduct help
 

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,

Sumproduct help
 

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,