Sum with multiple column lookup from multiple sheets with multiple criteria

[DickieD]

Hi All -- Been a whiiiiiile!

Think my brain is just fried or something; but I can't get this and rather than continue to wrack my brain figured I should just pose the question here.

I need to get a sum based on all column values of one sheet (call it "VALUESHEET") that are either NOT found in another column on another sheet (call it "CONTROLSHEET") or if they are found in that column, the value in another column on that second sheet is not a wildcard-driven constant.

Makes perfect sense right??

Said a different way: I want to "Sum" the values in Column D of my VALUESHEET if the values in the corresponding row of Column C on the VALUESHEET do not appear in Column A of my CONTROLSHEET *ONLY IF* if the value of any match in Column B of the control sheet is a specific wildcard-enabled constant (or a "Left"-first letter function can work if needed). And yes: the two sheets will always have a differing number of rows

Here's an example of what I have right now to get me up to the "*ONLY IF*" part:

=SUMPRODUCT(--(VALUESHEET!$A$2:$A$1000={A Constant}),--(VALUESHEET!$B$2:$B$1000={A Constant}),--(ISERROR(MATCH(VALUESHEET!$C$2:$C$1000*1,CONTROLSHEET!$A$2:$A$500,0))),VALUESHEET!$D$2:$D$1000)

But I need to somehow get a CONTROLSHEET!$B$2:$B$500<>"{CONSTANT}*" in there

I'm figuring there's got to be some simple way without having to use some type of additional matrix argument in the formula or something... but I just can't figure it out!

Let me know if I need to sample it out if my explanation doesn't do the trick. Thanks!

 

[DickieD]

Quick Bump guys

Just to add, I did figure out a loooooooong-way-around formula that kinda backs into the number.... but for 20000 rows it takes a good 100+ seconds to run (on a Quad-core) -- and I just KNOOOOOOW there has to be a better way than that!

But here's what I got to anyway:

=SUMPRODUCT(--(VALUESHEET!$A$2:$A$1000={A Constant}),--(VALUESHEET!$B$2:$B$1000={A Constant}),--(ISERROR(MATCH(VALUESHEET!$C$2:$C$1000*1,CONTROLSHEET!$A$2:$A$500,0))),VALUESHEET!$D$2:$D$1000)+SUMPRODUCT(COUNTIFS(VALUESHEET!$C$2:$C$1000,CONTROLSHEET!$B$2:$B$500,VALUESHEET!$B$2:$B$1000,{A CONSTANT},VALUESHEET!$A$2:$A$1000,{A CONSTANT}),--(LEFT(CONTROLSHEET!$B$2:$B$500,1)<>"O"),CONTROLSHEET!$C$2:$C$500)

I'm all eyes for anything you guys have up your sleeves to improve.

Thanks