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

 

[Aladin Akyurek]

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"]
<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]

It sees I fail to make clear what I'm asking for.

To recap: Are the values under the header Total the expected values? If so, how do you get 2 for Job Site 1 and 200?

 

[PlumGr8]

Ooo, ok sorry think i misunderstood. So in another sheet we have a column that we enter these MX values in to. It goes on for days, weeks months, even yrs sometimes. So i am just trying to create a formula that will scan the column these are entered in and count a tally. So Job Site 1 did X amount on this, or X amount of that over a long term span.

 

[Aladin Akyurek]

Ooo, ok sorry think i misunderstood. So in another sheet we have a column that we enter these MX values in to. It goes on for days, weeks months, even yrs sometimes. So i am just trying to create a formula that will scan the column these are entered in and count a tally. So Job Site 1 did X amount on this, or X amount of that over a long term span.

Why don't you answer my question for just the sample you post? Are the values (the numbers) under the header Total the expected counts for the sample you posted? If not, what is the count you want for Job Site 1 and 200 for the sample you posted?

 

[PlumGr8]

Those counts are current at this time, since i have been playing with these formulas. The Total #2 next to the 200 just mean it was completed 2 times so far

 

[Aladin Akyurek]

Those counts are current at this time, since i have been playing with these formulas. The Total #2 next to the 200 just mean it was completed 2 times so far

I think I should give up... Last effort:

Are you looking for counting the occurrences of 200 for the last sample you posted, if so, what must be the result?

Are you looking for counting the co-occurrences of 200 and Job Site 1 for the last sample you posted, if so, what must be the result?

 

[PlumGr8]

Every time Job Site 1 does a 200 it counts 1, Every time Job Site 2 does a 200, it counts 1, and just keeps a running tally. Over the months it will result in hundreds of overall

 

[Aladin Akyurek]

Every time Job Site 1 does a 200 it counts 1, Every time Job Site 2 does a 200, it counts 1, and just keeps a running tally. Over the months it will result in hundreds of overall

You have 2 for the co-occurrence of Job Site 1 together with 200 for the last sample you posted is 1, not 2. What is required is a simple yes or no. If the answer is no, I stop.

 

[PlumGr8]

No its 2, because it happened twice. The example is just a test version pulling data from the other cells. I have it mostly working thanks to the help from all of you, all i am having a glitch with is getting it to see 200 and 2000 at certain times. Its a rare occurrence but it does happen on occasion so i need the formulas to account for that.

 

[Aladin Akyurek]

No its 2, because it happened twice. The example is just a test version pulling data from the other cells. I have it mostly working thanks to the help from all of you, all i am having a glitch with is getting it to see 200 and 2000 at certain times. Its a rare occurrence but it does happen on occasion so i need the formulas to account for that.

This is the sample you posted:

Book1
	A	B	C
1	Site	Mx	Total
2	Job Site 1	200	2
3	Job Site 1	400/800	3
4	Job Site 1	2000	0
5	Job Site 1	300	1
6	Job Site 2	200	2
7	Job Site 2	400/800	2
8	Job Site 2	2000	0
9	Job Site 2	300	0
10	Job Site 3	200	1
11	Job Site 3	400/800	3
12	Job Site 3	2000	0
13	Job Site 3	300	0
Sheet1

In this sample Job Site 1 and 200 (colored above) co-occur (occur together) just once (that is, 1).

Under the header Total you have 2 for the co-occurrence of Job Site 1 and 200. Is this 2 true or the outcome of some non-working formula? If 2 is the true outcome for the above sample, I don't see it. If it's the outcome of a non-working formula, you should not post such at all. Instead, you should just post what is true for the above sample.

 

[PlumGr8]

Its showing 2 because it has occurred twice over a few weeks time. That example is how the columns/rows are formatted to look, which will then feed another sheet showing the results in more detail, That setup is actually in column X,Y and Z. The values for Z pull from column K. The formulas i have been getting great help from all of you with are in column Z in my sheet, which is where the 2,3,0, etc come from.