Question Locked

sum with two logical tests in excel

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi I am trying to sum cells that meet two conditions. This is the formula I was trying to use but it gives me 0. Please help. So if cells b29-253 greater than 0 and if cells j29-253 = Bay Insulation (1%10/30 50k) than sum the amounts in cells g29-253. This should give me an amount but it comes back with just 0?
=SUM(IF($B$29:$B$253>0,IF($J$29:$J$253="Bay Insulation (1%10/30 50k)",$G$29:$G$253)))

3 replies

by bizzo

=IF(COUNTIF($B$29:$B$253,">0")=225,IF(COUNTIF($J$29:$J$253,"Bay Insulation (1%10/30 50k)")=225,SUM($G$29:$G$253),"not all bays"),"not all >0")

A bit contrived, but, if I understand what you're after correctly, this adds up the G numbers, if all the B numbers are > 0, and if all the J cells say "Bay Insulation (...)"

or were you after adding each G number if B>0 and J="Bay ..."

by neon samurai

For your code, you need an array aware formula. If you open the cell for editing (f2) then hold Crtl+Shift and press enter to close the cell for editing, it should show an extra set of brackets "{ }". The cell formula will then be aware that it needs to look through all rows in $b$29:$b$253 and test for logic ">0".

If you already knew that then disregard but if not, that may fix it for you.

This discussion is closed to new replies.