SUMIF problems

[Guest]

Hello again all,
I'm WAY over my head on this, but... I am trying to calc the average age of
a issue ticket. Open column C is the SEVERITY LEVEL. Open column D is the
time created, example 10/09/2006 10:16:47.

This formula works (in Excel it has { } around it.

=(NOW()-(SUMIF(Open!C1:C983,B16,Open!D1983)/COUNTIF(Open!C1:C983,B16)))*24

I am trying to replace NOW() with Cell C1. C1 is a static date and time,
example 10/10/06 10:40 AM

I have this formula, but it returns values that are WAY off. Thoughts?

 

[Guest]

Sorry,
It may be helpful to put the values I am getting, and formula's i'm using.

Column D has only one value, and it is 10/09/2006 10:16:47
Cell C1 has the value of 10/10/06 10:40 AM

The value returned from formula

=((C1)-(SUMIF(Open!C1:C816,B16,Open!D1816)/COUNTIF(Open!C1:C816,B16)))*24

is
936010.6667


The value returned from formula
=(NOW()-(SUMIF(Open!C1:C983,B16,Open!D1983)/COUNTIF(Open!C1:C983,B16)))*24

is
26.51808889

 

[Guest]

Your result is consistent with the SUMIF/COUNTIF part of the formula
returning zero as

C1*24 will be equal to approx 936011.

I note you have different ranges, have you excluded the one qualifying date?

Note: your formula isn't an array formula, it doesn't require { and }. If
you want an array formula to do the same....

=(C1-AVERAGE(IF(open!C1:C983=B16,open!D1983)))*24

 

[Guest]

Thank you for the response.
Based on your post, i used your formula, but inconsistant results. With
real data, it was close to the actual, but not correct result. So I
populated the open tab with sample data. Open tab rows 2 thru 16, column C
with the number 1, column D with 10/10/2006 15:52
on the tab with the formula, cell c1 value is 10/10/06 5:00 PM, cell b16 is
the number 1.

Here is a manual intensive formula that works.
=(C1-(AVERAGE(Open!D216)))*24
value returned is 1.1 hours

I am looking for a formula that will evaluate the open tab, column C for the
matching value in cell b16 and then pull in the value of column D (open tab).
Cell C1 minus the Average all the values of column D (open tab), then
multiply by 24.

Here is the formula you created, but it returns the value of
936017.0

VERY ODD thing does occur, i click on the cell that has the value 936017.0 I
then click on fx next to tool bar, a dialog box titled function Argument
appears. In this dialog box, it says Formul result = 1.1 hours. Why can I
not get it to display 1.1 hours like is displayed in the functional argument
dialog box?

Sorry for the long ol story, but hopefully it all makes sense.


David