# 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.

2. ## Re: Sumifs without a matching criteria

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

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))

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.

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.

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.

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

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

##### Users Browsing this Thread

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1