# Sumifs without a matching criteria

1. ## 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.  Register To Reply

2. ## Re: Sumifs without a matching criteria

If I understand correctly try
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Sumifs without a matching criteria

Maybe?

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

4. ## Re: Sumifs without a matching criteria

Phuocam

It's almost working but the answer I should get is 9 not 21. I understand what the formula is doing to get 21, but I need for the answer to be 9.  Register To Reply

5. ## Re: Sumifs without a matching criteria

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.  Register To Reply

6. ## Re: Sumifs without a matching criteria

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.  Register To Reply

7. ## Re: Sumifs without a matching criteria

Here's another one to try:

=SUMIFS(\$O\$1:\$O\$16,\$N\$1:\$N\$16,if(countif(\$N\$1:\$N\$16,"Yes"),"Yes","*"),\$M\$1:\$M\$16,if(countif(\$M\$1:\$M\$16,"Yes"),"Yes","*"),\$L\$1:\$L\$16,if(countif(\$L\$1:\$L\$16,"Yes"),"Yes","*"))

Hope this helps.

Pete  Register To Reply

8. ## Re: Sumifs without a matching criteria 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  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 