limiting SUMPRODUCT to a certain value

[PlumGr8]

So, i have this formula =sumproduct((H2:H500,"Job 1")+(K2:K500,"200"))

Problem is sometimes K says 200, others its 200/tire, etc. Is there a simple way that anytime it see "200" that it will count?

Basically i have MX events to also track and trying to automate the tracking. I did try "*200*" and a couple other things but couldn't quite get it. Unless there is a different formula that is better i am all ears. Thanks in advance

 

[arthurbr]

Perhaps =sumproduct((H2:H500,"Job 1")+isnumber(search(K2:K500,"200")))

 

[steve the fish]

Do you mean H2:H500 equals Job 1 and K2:K500 starts with 200? Maybe:

=SUMPRODUCT(--(H2:H500="Job 1"),--(LEFT(K2:K500,3)="200"))

 

[PlumGr8]

So that one works pretty well so far. Honestly, not sure what the "left" part is referring to? But that formula does work better for my needs compared to the previous one as that one i can also use it for a few text options.

Now a follow up. And i think it wil require a macro which i no virtually nothing about, and might be similar to the one that Trevor is helping me well in another thread. But, say i have another sheet referencing these new value calculation. Nothing crazy, just another sheet that shows what site did what. Is there a way for the count to continue even after the data is deleted from the source sheet? So, trying to work on a macro that will auto delete the data after say 75 days that this formula will pull from. So, is there a way to keep the total on the new sheet without resetting to zero when the data is erased?

 

[PlumGr8]

Another question, trying to modify it a little bit. If i want it to see ="200" or 250"" how can i add that so that if it sees either of those 2 values it counts? I tried playing with the OR function, but was getting errors.

 

[DanteAmor]

Go the 2 formulas:

=SUMPRODUCT((H2:H500="Job 1")*(iSNUMBER(SEARCH({"200","250"},K2:K500))))

=SUMPRODUCT((H2:H500="Job 1")*(LEFT(K2:K500,3)={"200";"250"}))

 

[PlumGr8]

Awesome, i need to learn more of this stuff haha. The 2nd option didn't work this time, but the first one did. I like the 2nd because its similar to the other ones now, but i kept getting n/a values on that one. No i just need to get help with the macro. This forum is awesome. Thanks again

 

[DanteAmor]

You're welcome and thanks for the feedback.

 

[PlumGr8]

ok, another question haha. So i am building out the formula for the events, and i have 200 and 2000. But not always entered at the same time. So i have the 2000 one using the formula and limiting to 4 spaces, so that one always enters correctly when it sees 2000. But, is there a way to get the 200 one to ignore when it sees 2000?

 

[DanteAmor]

Try this:

=SUMPRODUCT((H2:H500="Job 1")*(LEFT(K2:K500,3)={"200";"250"})*LEFT(K2:K500,4)<>"2000"))