Log In
Register
Home
Active Member
June 16, 2013
Offline
Respeted sir,
When I usiby SUMPRODUCT Formula In range in given Example
Blank cell is there formula not working. My questions.
1. Howmany students falied in One Subject/ Two Subjects?
2. When blank cells in sheet ( in a rage) SUMPRODUCT Formula working/Not working?
Thankyou
July 16, 2010
Offline
Hi Kondempudi,
Welcome to our forum!
Your SUMPRODUCT formulas have spaces, not blanks. i.e. your formula is like this:
=SUMPRODUCT((I4:I38=" ")+(O4:O38=" ")+(U4:U38=" ")+(AA4:AA38=" ")+(I4:I38<D403)+(O4:O38<D42)+(U4:U38<D42)+(AA4:AA38<D42))
It should be this if you're testing for blank cells:
=SUMPRODUCT((I4:I38="")+(O4:O38="")+(U4:U38="")+(AA4:AA38="")+(I4:I38<D403)+(O4:O38<D42)+(U4:U38<D42)+(AA4:AA38<D42))
Mynda
Active Member
June 16, 2013
Offline
Respected Madam,
With help of your advice I tried it last 4 days( tried and tried) reached to succeed.
D42=18,E42=0
Two subjects failed formula
=SUMPRODUCT((($I$4:$I$38<$D$42)*($I$4:$I$38>$E$42)+($O$4:$O$38<$D$42)*($O$4:$O$38>$E$42)+($U$4:$U$38<$D$42)*($U$4:$U$38>$E$42)+($I$4:$I$38<$D$42)*($AA$4:$AA$38>$E$42)>=2)*1)
One subject failed formula
=SUMPRODUCT((($I$4:$I$38<$D$42)*($I$4:$I$38>$E$42)+($O$4:$O$38<$D$42)*($O$4:$O$38>$E$42)+($U$4:$U$38<$D$42)*($U$4:$U$38>$E$42)+($I$4:$I$38<$D$42)*($AA$4:$AA$38>$E$42)=1)*1)
Thank you and Regards.
1 Guest(s)
