I am using the formula below to count the number of rows that meet all the
conditions.

=SUMPRODUCT(--('Enroll I'!$C$2:$C$2921<"Car"),--('Enroll
I'!$K$2:$K$29210),--('Enroll I'!$M$2:$M$2921="Yes"),--('Enroll
I'!$N$2:$N$2921="Yes"),--('Enroll I'!$P$2:$P$2921="Trim"),--('Enroll
I'!$H$2:$H$2921=J2925),--('Enroll I'!$H$2:$H$2921<=L2925))


I would like to change it around to get an average. If the conditions above
are true, I would like to get the average in L2:L2921. Any ideas to write
this complex if statement?

Try the below and feedback. Please note that this is an array formula. Within
the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to
apply this formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=AVERAGE(IF(
('Enroll I'!$C$2:$C$2921<"Car")*
('Enroll I'!$K$2:$K$29210)*
('Enroll I'!$M$2:$M$2921="Yes")*
('Enroll I'!$N$2:$N$2921="Yes")*
('Enroll I'!$P$2:$P$2921="Trim")*
('Enroll I'!$H$2:$H$2921=J2925)*
('Enroll I'!$H$2:$H$2921<=L2925)
,'Enroll I'!$L$2:$L$2921))

If this post helps click Yes
---------------
Jacob Skaria


"PAL" wrote:

I am using the formula below to count the number of rows that meet all the
conditions.

=SUMPRODUCT(--('Enroll I'!$C$2:$C$2921<"Car"),--('Enroll
I'!$K$2:$K$29210),--('Enroll I'!$M$2:$M$2921="Yes"),--('Enroll
I'!$N$2:$N$2921="Yes"),--('Enroll I'!$P$2:$P$2921="Trim"),--('Enroll
I'!$H$2:$H$2921=J2925),--('Enroll I'!$H$2:$H$2921<=L2925))


I would like to change it around to get an average. If the conditions above
are true, I would like to get the average in L2:L2921. Any ideas to write
this complex if statement?