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]

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.

I'm sorry. I don't think you are able to detach yourself from your actual file and from some "grand formulas" you think you have and to answer the questions on the basis of the sample you posted. The sample says it's 1 for the co-occurrence of Job Site 1 and 200 and you insist it's 2. So I now must give up and wish you good luck.

 

[DanteAmor]

@Aladin Akyurek, Sorry to leave you alone on this path, I did not understand the last requirement either.

 

[PlumGr8]

You are under the assumption that the sample i posted is all of the data. It is not. It is 2 because at the time, it has occurred twice, now since the beginning of this thread it is up to 7.

So now it says

Job Site 1 200 7

It turns out to be really simple formulas, that i just am unfamiliar with that you both have helped me with a lot. I'll play with the formulas you both have given, see if i can get it finished up. If need be, i'll post another thread seeing if another member has any other insight in to the last issue with counting 200 & 2000 when they show up together. Thanks again for everything

 

[Aladin Akyurek]

You are under the assumption that the sample i posted is all of the data. It is not. It is 2 because at the time, it has occurred twice, now since the beginning of this thread it is up to 7.

So now it says

Job Site 1 200 7

It turns out to be really simple formulas, that i just am unfamiliar with that you both have helped me with a lot. I'll play with the formulas you both have given, see if i can get it finished up. If need be, i'll post another thread seeing if another member has any other insight in to the last issue with counting 200 & 2000 when they show up together. Thanks again for everything

The question is simple:

Is the co-occurrence count for Job Site 1 and 200 for the sample you posted is 1 or not?

I say 1 for the posted sample. Also here an advise: You should just post the expected values for the sample only that you posted under the header total.

When you then get a formula proposal, you should test it with the posted sample and with your full data.

 

[PlumGr8]

Ironically, what you asked for is exactly what i posted.

The example is 2, not 1, and now its 7, not 2.

The values posted are from the formulas that have been provided in this thread. Tested and verified and those totals are what is provided by the formula.

My only hick-up is, that if i type 200/2000, it recognizes 200 but not 2000, if i type 2000/200 it see the 2000 and the 200. Issue is i can't guarantee that everyone will type 2000 first, so i just need a formula that when it sees "2000" anywhere in column K that it counts it.