limiting SUMPRODUCT to a certain value

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
87
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
Joined
Dec 8, 2006
Messages
2,224
Office Version
  1. 2010
Perhaps =sumproduct((H2:H500,"Job 1")+isnumber(search(K2:K500,"200")))
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,400
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 23, 2017
Messages
87
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
Joined
Nov 23, 2017
Messages
87

ADVERTISEMENT

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
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Nov 23, 2017
Messages
87

ADVERTISEMENT

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
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
You're welcome and thanks for the feedback.
 

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
87
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
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
Try this:

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

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top