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.

 

[LawNapier]

FormR - Ugh you are right. I thought I had this licked. Let me work with your SUMPRODUCT solution and get back with you. Thanks

 

[LawNapier]

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)

Hi FormR --

The concept example I gave you above was to simplify things to explain my issue.

Below is the actual code in my spreadsheet where I applied your SUMPRODUCT formula to.

I am getting the "There's a problem with this formula. Not trying to type a formula? ERROR" when I try to save the below to a call to run the calculation.

Can you take a look and let me know what you think I have written wrong? I think I have the syntax correct from your example, but not sure.

=SUMPRODUCT(SUMIFS

('RAW DATA'!$P:$P,
'RAW DATA'!$E:$E,$B$5:$B$8,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$L:$L,$AG$6,
'RAW DATA'!$N:$N,$AH$4

))/

SUMPRODUCT(COUNTIFS

('RAW DATA'!$E:$E,$B$5:$B$8,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$L:$L,$AG$6,
'RAW DATA'!$N:$N,$AH$4

))

 

[FormR]

This commits for me, I've not checked the logic per se.

=SUMPRODUCT(SUMIFS('RAW DATA'!$P:$P,'RAW DATA'!$E:$E,$B$5:$B$8,'RAW DATA'!$K:$K,"INC",'RAW DATA'!$L:$L,$AG$6,'RAW DATA'!$N:$N,$AH$4))/SUMPRODUCT(COUNTIFS('RAW DATA'!$E:$E,$B$5:$B$8,'RAW DATA'!$K:$K,"INC",'RAW DATA'!$L:$L,$AG$6,'RAW DATA'!$N:$N,$AH$4))