=SUMPRODUCT('M:\[Ryder.xlsx]Bills16'!$K$4:$K$500,--('M:\[Ryder.xlsx]Bills16'!$G$4:$G$500<>0.09),--('M:\[Ryder.xlsx]Bills16'!$N$4:$N$500<>"license"),--('M:\[Ryder.xlsx]Bills16'!$B$4:$B$500=A97),--('M:\[Ryder.xlsx]Bills16'!$N$4:$N$500<>"lea*"))
Above is a formula that works, except for the final array (Array 5). Before I go back to using SUMIFS and having to open the Ryder.xlsx workbook for it to work, does anyone have any clues as to why Array 5 is not working?
Thanks,
TR
What does "not work" mean?
You can't use <> (or =) with a wildcard - try using SEARCH, FIND or LEFT.
Perhaps something like this.
=SUMPRODUCT('M:\[Ryder.xlsx]Bills16'!$K$4:$K$500,--('M:\[Ryder.xlsx]Bills16'!$G$4:$G$500<>0.09),--('M:\[Ryder.xlsx]Bills16'!$N$4:$N$500<>"license"),--('M:\[Ryder.xlsx]Bills16'!$B$4:$B$500=A97),--(LEFT('M:\[Ryder.xlsx]Bills16'!$N$4:$N$500,3) <>"lea"))
Last edited by Norie; 11-08-2016 at 04:38 PM.
If posting code please use code tags, see here.
Are you trying to use the * as a wildcard?<>"lea*"))
If so, SUMPRODUCT doesn't work with wildcards.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
It is not excluding cells that start with "lea" from the product. It does, however, exclude summing cells that have "license" in them (Array 3).
Oops, I made some mistakes in the formula I posted.
Thanks, I thought I had tried that, but I think there was a space after the "lease xtra" on the source file. Now it hums...
Besides the space, could you elaborate on this??Originally Posted by Norie
Oops, I made some mistakes in the formula I posted.
Bit confused, I got the logic of the last array the wrong way round by accident - there was no space involved.
What exactly are you trying to do with the last array and where does the space come into it?
Do the double dashes before arrays 2 thru 5 make Excel process this formula as an array? I tried Ctrl-Shift-Enter and got the braces, but they didn't change anything...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks