Hi Dave,
Thank you again for your prompt answer.
I tried the formula you wrote down in your last post. The problem is
that as soon as i write a condition such as =time(15;30;0) or ="sb" in
the formula, it returns 0. If I remove conditions from the formula it
returns a result.
Also, I looked at formula help menu. It is said there that if one of
the arrays are not numeric the formula considers them as zeros. I think
this is an additional problem for using this formula, because one of
arrays are letter /ex. SB/.
Do you think I could use another formula, instead of SUMPRODUCT?
In my case, lets say, I need two levels of data filtering. The first
level is to filter data by the condition "SB", and the second level is
to filter the already filtered data by the condition "time". After that
I have to find the average minutes a process lasts after a specific
time, say 15:30:00.
For example:
Time In duration process
10:23:12 2:45 SB
10:45:05 1:12 AA
10:56:25 0:56 SB
15:45:14 5:23 SB
What if I use IF functions? Can I manage this in one cell?
Best regards,
kras
Dave Peterson Wrote:
This formula:
=sumproduct(--(a1:a10="something"),--(b1:b10="another"),c1:c10)
expects to find numbers in C1:C10. If you have text/errors in that
range, then
you'll have trouble.
(You'd have to adjust the ranges to match your data.)
But from what you wrote in your followup, you're only looking at one
criteria.
So maybe something like:
=SUMIF(C1:C10,"SB",B1:B10)/COUNTIF(C1:C10,"SB")
Oopsie....
You wanted it after 15:30:00, so back to =sumproduct()
=sumproduct(--(a1:a10=time(15,30,0)),--(c1:c10="sb"),b1:b10)
/ sumproduct(--(a1:a10=time(15,30,0)),--(c1:c10="sb"))
All one cell.
That assumes that the times in column A are really times--they don't
include
dates.
(Sum up all the duration times in column B that matches the criteria
and divide
it by the count that match both criteria.)
--
kras
------------------------------------------------------------------------
kras's Profile:
http://www.excelforum.com/member.php...o&userid=28667
View this thread:
http://www.excelforum.com/showthread...hreadid=483373