SUMIF Function

[BCS2]

Can multiple criteria be used with this function? I am using this function to find an average time based on the criteria being greater than a certain number and less than or equal to another cetain number.

Is there another function available to accomplish this?

 

[just_jon]

Does this help -- http://www.mrexcel.com/board2/viewtopic.php?t=79363

 

[fairwinds]

..and of course:

http://www.mrexcel.com/board2/viewtopic.php?t=59063

 

[gaftalik]

Hi ,

try average(if(and(logicalone;logical2);your range)) entered as an array with Ctrl, shift and enter

G.Luck

 

[BCS2]

gaftalik,

Here is the formula you suggested.

=AVERAGE(IF(AND(K:K>&A59;K:K<=&A60);K:K))

It tells me that there is an error in my formula. I tried entering as an array, no luck. Column K contains times in [H]:MM format. I am trying to sum all the times within a range of time (two criteria), and then find the average.

 

[gaftalik]

Oops, sorry it is not the formula you need , now i understood better.

 

[fairwinds]

Enter formula with Ctrl + Shift + Enter

Book1
	A	B	C	D
1	Criteria	Time
2	1	1
3	2	2
4	3	3
5	4	4		3.5
6	5	5
7	6	6
8	7	7
Sheet5

 

[BCS2]

fairwinds:

This is the formula that you suggested.

{=AVERAGE(IF((K3:K62>$A59)*(K3:K62<=$A60),K3:K62))}

It seems to work so far. However, the amount of data that is entered into this sheet each week varies greatly. Instead of specifying a data range (K3:K62) I would like to include the entire K column. That way, when the data amount varies I won't have to change the formula every week.

Possible?

ray:

 

[fairwinds]

The formula don't like the whole column i.e. A:A but you can specify a range as large as you like as AVERAGE disregards text and empty cells.