We help IT Professionals succeed at work.

# I need an updated excel formula

on
OK, here is a softball for someone...

I need the following formula to EXCLUDE column B from the counts if Column B is blank. Column B contains text.

=SUMPRODUCT((MFDATA!\$C\$2:\$C\$5000=MFINFO!A6)*(MFDATA!\$H\$2:H\$5000+MFDATA!\$I\$2:I\$5000+MFDATA!\$N\$2:\$N\$5000=0)*(MFDATA!\$K\$2:\$K\$5000="N"))
Comment
Watch Question

## View Solution Only

Most Valuable Expert 2013
Commented:
Try adding another condition like this

=SUMPRODUCT((MFDATA!\$C\$2:\$C\$5000=MFINFO!A6)*(MFDATA!\$H\$2:H\$5000+MFDATA!\$I\$2:I\$5000+MFDATA!\$N\$2:\$N\$5000=0)*(MFDATA!\$K\$2:\$K\$5000="N")*(MFDATA!\$B\$2:\$B\$5000<>""))

regards, barry

Commented:
Do you need to check the entire B column or just a specific cell? You could easily use the if() formula to test if a cell or range of cells is blank, and either have two separate Sumproduct() equations, or have the if() return a 0 that is added to the existing equation.

Commented:
Works great!