We help IT Professionals succeed at work.

I need an updated excel formula

wrt1mea
wrt1mea asked
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

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.

Author

Commented:
Works great!