Sumifs without a matching criteria

Hello All, I am wondering if there's a formula I could use like a sumifs that would add all the data even if a particular criteria is not found in the range.

Example:

=SUMIFS($O$1:$O$16,$N$1:$N$16,"Yes",$M$1:$M$16,"Yes",$L$1:$L$16,"Yes")

The above would sum all the numbers if columns N,M,L contain "Yes" in them, but what if column M doesn't contain "Yes" I would like the sum of columns N and L still. Is there a formula to make this happen? any help would be appreciated.

Maybe?

=SUMPRODUCT($O$1:$O$16*(($N$1:$N$16="Yes")+($M$1:$M$16="Yes")+($L$1:$L$16="Yes")>0))

FlameRetired

This would work, but is not dynamic enough because my original data is about 9 columns and I would have to do about 36 combinations with this method.

Try this:

=SUMPRODUCT($O$2:$O$17*(($L$2:$L$17=E2)+($M$2:$M$17=E2)+(N2:$N$17=E2)>1))

If you have multiple combinations, you can use the helper column.

Originally Posted by Phuocam

Try this:

=SUMPRODUCT($O$2:$O$17*(($L$2:$L$17=E2)+($M$2:$M$17=E2)+(N2:$N$17=E2)>1))

If you have multiple combinations, you can use the helper column.

ok this work perfect!! thank you so much