AVERAGE HH:MM time using OR and AND together within the same formula

[LawNapier]

I am trying to average column D (time results in custom format HH:MM).

I need to only average the rows that meet the criteria.

A:A = APPLE or PEAR
and B:B= RIPE and C:C= GOOD

I need the result to return in custom format HH:MM

	A	B	C	D
1	APPLE	RIPE	GOOD	01:23
2	APPLE	RIPE	GOOD	15:33
3	PEAR	RIPE	GOOD	22:00
4	PEAR	ROTTEN	BAD	14:47
5	APPLE	ROTTEN	BAD	00:48

Thank you in advance for your help.

 

[Tricepsratops]

Custom number format: [h]:mm:ss, this is the most important part or it will just sum the hours like a clock instead of as a number.

Here I give you an example of 2 criterias , if you need 3 criterias (+ APPLE or PEAR) cause you have many other fruits and cant do it by yourself tell me and I will help you.

Traductor de Formulas - copia.xlsm
	A	B	C	D	E	F	G
1	APPLE	RIPE	GOOD	1:23:00		Criteria1	Criteria2
2	APPLE	RIPE	GOOD	15:33:00		RIPE	GOOD
3	PEAR	RIPE	GOOD	22:00:00
4	PEAR	ROTTEN	BAD	14:47:00
5	APPLE	ROTTEN	BAD	0:48:00
6				12:58:40	Average of criteria
Hoja4

Cell Formulas
Range	Formula
D6	D6	=AVERAGEIFS(D1:D5,B1:B5,"="&F2,C1:C5,"="&G2)

 

[LawNapier]

Hi Tricepsratops - Thanks for your response. However, the premise of the question is how I can combine an "or" and "and" into one formula. I need to know how to look for the below.

A:A = APPLE or PEAR
and B:B= RIPE and C:C= GOOD

You streamlined it by not including the "OR" component. I understand why you did that, but I need to include the "OR" component and that is the part I can't figure out. Any ideas how to do that?

 

[Tricepsratops]

I dont really know how to make it work, I tried but everytime I write OR function or AND it breaks.

Lets wait for someone more experienced.

 

[FormR]

Hi, you could try like this.

Book2
	A	B	C	D	E	F
1	APPLE	RIPE	GOOD	01:23		12:58
2	APPLE	RIPE	GOOD	15:33
3	PEAR	RIPE	GOOD	22:00
4	PEAR	ROTTEN	BAD	14:47
5	APPLE	ROTTEN	BAD	00:48
Sheet1

Cell Formulas
Range	Formula
F1	F1	=SUM(SUMIFS(D:D,A:A,{"APPLE","PEAR"},B:B,"RIPE",C:C,"GOOD"))/SUM(COUNTIFS(A:A,{"APPLE","PEAR"},B:B,"RIPE",C:C,"GOOD"))

 

[LawNapier]

Hi FormR - When using the {"APPLE","PEAR"} Array, I need to use cell references instead of manually placing the criteria string
Apple" and "Pear" in there. Do you know what the syntax would be for that?

 

[LawNapier]

Tricepsratops - Thanks for trying. This has been a tough one for me as well.

 

[FormR]

I need to use cell references instead of manually placing the criteria

Hi, you could give this a try.

Book3
	A	B	C	D	E	F	G
1	APPLE	RIPE	GOOD	01:23:00		APPLE	12:58:40
2	APPLE	RIPE	GOOD	15:33:00		PEAR
3	PEAR	RIPE	GOOD	22:00:00
4	PEAR	ROTTEN	BAD	14:47:00
5	APPLE	ROTTEN	BAD	00:48:00
Sheet1

Cell Formulas
Range	Formula
G1	G1	=SUMPRODUCT(SUMIFS(D:D,A:A,F1:F2,B:B,"RIPE",C:C,"GOOD"))/SUMPRODUCT(COUNTIFS(A:A,F1:F2,B:B,"RIPE",C:C,"GOOD"))

 

[LawNapier]

I ended up using thanks.

=SUM(

AVERAGEIFS(C:C,
A:A,"="&"APPLE",
B:B,"="&"RIPE"),

AVERAGEIFS(C:C,
A:A,"="&"PEAR",
B:B,"="&"RIPE"),


)

 

[FormR]

I ended up using

OK, as long as you understand that is not the same as the average of the times where column A is either Pear OR Apple.

To demonstrate.

Book1
	A	B	C	D	E	F	G	H
1	APPLE	RIPE	01:23:00		APPLE	12:58:40	06:28:00	12:58:40
2	APPLE	RIPE	15:33:00		PEAR
3	PEAR	RIPE	22:00:00
Sheet1

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(SUMIFS(C:C,A:A,E1:E2,B:B,"RIPE"))/SUMPRODUCT(COUNTIFS(A:A,E1:E2,B:B,"RIPE"))
G1	G1	=SUM(AVERAGEIFS(C:C, A:A,"="&"APPLE", B:B,"="&"RIPE"), AVERAGEIFS(C:C, A:A,"="&"PEAR", B:B,"="&"RIPE"))
H1	H1	=AVERAGE(C1:C3)