limiting SUMPRODUCT to a certain value

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
72
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
1,999
Office Version
2010
Perhaps =sumproduct((H2:H500,"Job 1")+isnumber(search(K2:K500,"200")))
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
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
72
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
72
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
11,867
Office Version
2007
Platform
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
72
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
11,867
Office Version
2007
Platform
Windows
You're welcome and thanks for the feedback.
 

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
72
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
11,867
Office Version
2007
Platform
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,099,787
Messages
5,470,781
Members
406,720
Latest member
tylergaps

This Week's Hot Topics

Top