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

 

[DanteAmor]

Can you give examples of when to count and when not to count?

 

[Aladin Akyurek]

=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?

 

[DanteAmor]

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

 

[PlumGr8]

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.

 

[DanteAmor]

I still do not understand what data you have in the cell and if you want to include or exclude the 2000

 

[PlumGr8]

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

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Site[/TD]
[TD="width: 64"]Work[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Total[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Job Site 1[/TD]
[TD]200/2000[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job Site 2[/TD]
[TD]200/tires[/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job Site 2[/TD]
[TD]300/2000[/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job Site 1[/TD]
[TD]400/tires[/TD]
[TD][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

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

[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64"]Site[/TD]
[TD="width: 64"]Work[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Total[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Job Site 1[/TD]
[TD]200/2000[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job Site 2[/TD]
[TD]200/tires[/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job Site 2[/TD]
[TD]300/2000[/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job Site 1[/TD]
[TD]400/tires[/TD]
[TD][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[PlumGr8]

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

 

[Aladin Akyurek]

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.

 

[PlumGr8]

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[TABLE="width: 198"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 3"]MX Events[/TD]
[/TR]
[TR]
[TD]Site[/TD]
[TD]Mx[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Job Site 1[/TD]
[TD]200[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Job Site 1[/TD]
[TD]400/800[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Job Site 1[/TD]
[TD]2000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Job Site 1[/TD]
[TD]300[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Job Site 2[/TD]
[TD]200[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Job Site 2[/TD]
[TD]400/800[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Job Site 2[/TD]
[TD]2000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Job Site 2[/TD]
[TD]300[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Job Site 3[/TD]
[TD]200[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Job Site 3[/TD]
[TD]400/800[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Job Site 3[/TD]
[TD]2000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Job Site 3[/TD]
[TD]300[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]