limiting SUMPRODUCT to a certain value

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
136
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
 
Can you give examples of when to count and when not to count?
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
=SUM(IF(H2:H500="Job 1",IF(ISNUMBER(MATCH(IF(MID(K2:K500,4,1)="0","###",LEFT(K2:K500,3)),{"200","250"},0)),1)))

That one was working for me. Not sure why yours won't for me? The other thing i need to decide on is most times i have 200 or 200/tires, etc. Sometimes i have 200/2000. So it will see the 200, but not the 2000 when i need it to count the 2000. I have no idea if a formula can account for that. I may just have to track that one separately?

I would advise to install this array-processing formula. By the way, are we trying to include both 200 and 2000 at the same time?
 
Upvote 0
I would advise to install this array-processing formula. By the way, are we trying to include both 200 and 2000 at the same time?

That formula needs the user, but I did not understand if he wants to include the 2000
 
Upvote 0
Sorry, i know its a little weird and i really thank both of you for all the help. I can do basic stuff but all this is more then i am use to and i def need to learn more.

So, we have set maintenance schedules that we do, most are set at 200, 300 etc. Some get entered as 200/300. Issue i run in to as i got almost everything to work, but i run into a conflict when i have say 200/2000. It will see one, and not the other. the 2000 isn't nearly as often, so if a formula for that one can't be easily reached then no worries at all, i can just ignore that one and keep it at just 200.

So for example
Job Site 1....200
Job Site 2....200/2000
Job Site 3.....400/tires

etc.
 
Upvote 0
I still do not understand what data you have in the cell and if you want to include or exclude the 2000
 
Upvote 0
The data is just the job site and the work performed. 200, 300, 500, 2000 etc. Most times it is 200/tires, 200/batteries, so i am only tracking the numbers. So when i do just a 200, i need it to just count 200. But sometimes, but not often, i have say 200/2000, or 2000/300. I wish i could get a screen shot, but the files are on a locked computer only on intranet, so i have to bounce back and forth between computers. But something along these lines i am trying to do

SiteWorkTotal
Job Site 1200/20002002
Job Site 2200/tires3001
Job Site 2300/20004001
Job Site 1400/tires20002

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
The data is just the job site and the work performed. 200, 300, 500, 2000 etc. Most times it is 200/tires, 200/batteries, so i am only tracking the numbers. So when i do just a 200, i need it to just count 200. But sometimes, but not often, i have say 200/2000, or 2000/300. I wish i could get a screen shot, but the files are on a locked computer only on intranet, so i have to bounce back and forth between computers. But something along these lines i am trying to do

SiteWorkTotal
Job Site 1200/20002002
Job Site 2200/tires3001
Job Site 2300/20004001
Job Site 1400/tires20002

<tbody>
</tbody>

The count of just 200 would be 2, but the count of Job Site 1 and 200 should be 1 for the sample you posted, right?
 
Upvote 0
Yes, in the full setup it goes

job site 1 total
200 2
300 3
2000 1
Job Site 2
200 4
300 3
2000 2

and so on
 
Upvote 0
Yes, in the full setup it goes

job site 1 total
200 2
300 3
2000 1
Job Site 2
200 4
300 3
2000 2

and so on

Are these for the sample you posted, if so they must be wrong. I'm asking for the expected values for the sample you posted not for "the full setup" which I cannot see.
 
Upvote 0
It ends up in the cell like this. The source column these pull from may say 200/2000, or 300/2000, or 200/tires, 300/batts, etc
MX Events
SiteMxTotal
Job Site 12002
Job Site 1400/8003
Job Site 120000
Job Site 13001
Job Site 22002
Job Site 2400/8002
Job Site 220000
Job Site 23000
Job Site 32001
Job Site 3400/8003
Job Site 320000
Job Site 33000

<colgroup><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top