# limiting SUMPRODUCT to a certain value

#### PlumGr8

##### Board Regular
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### arthurbr

##### Well-known Member
Perhaps =sumproduct((H2:H500,"Job 1")+isnumber(search(K2:K500,"200")))

#### steve the fish

##### Well-known Member
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

##### Board Regular
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

##### Board Regular

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

##### Well-known Member
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"}))

Last edited:

#### PlumGr8

##### Board Regular

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

##### Well-known Member
You're welcome and thanks for the feedback.

#### PlumGr8

##### Board Regular
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

##### Well-known Member
Try this:

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

Replies
5
Views
429
Replies
1
Views
111
Replies
1
Views
567
Replies
1
Views
634
Replies
3
Views
226

1,130,022
Messages
5,639,596
Members
417,100
Latest member
Simon123456789

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back