Many ifs....

P

PAL

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$2921>0),--('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?
 
J

Jacob Skaria

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$2921>0)*
('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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top