Trying to figure out how to exclude two items in the same range. I want the sum of Column M if Column G doesn't contain 767103 or 767104 (text values). I tried like below, but that doesn't give me the correct values (gives me the value of the whole column). Any help would be appreciated.
[CODE][/=SUMIFS($M$2:$M$1169,$G$2:$G$1169,"<>767104",$G$2:$G$1169,"<>767103")CODE]
Maybe this...Originally Posted by jomili
=SUMPRODUCT(--ISNA(MATCH($G$2:$G$1169,{"767104","767103"},0)),$M$2:$M$1169)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
And *Bingo*! You win the prize!
I follow it all except the "--" at the front. What's the purpose of that?
The SUMPRODUCT function works with numbers.
The ISNA function returns the Boolean values TRUE or FALSE. We have to convert those to numbers so SUMPRODUCT can work.
Using the double unary minus -- is one way to do that.
--TRUE = 1
--FALSE = 0
Of course! It's been awhile since I've used ISNA (I used to use it as "IF(ISNA(" until IFERROR came along) so didn't think of the TRUE and FALSE. Thanks so much for showing me a better way.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks