Sumproduct help

[Guest]

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!D1977<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>0.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,

 

[Guest]

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

http://msdn.microsoft.com/newsgroup...8ded&mid=d8e96fcc-920e-4095-a07a-090bb7563091

I tried this. Also get #Value error

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

 

[Roger Govier]

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

 

[Guest]

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.