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

 

[PlumGr8]

Hmm, tried that but was getting the typo warning and was correcting it, but then just #value

so i tried it that way, and tried it without the "250". so ...={"200"})*left(k2:k500,4)< >"2000"))

and it was detecting a typo and fixing it to ...)<"2000") with a #value error?

 

[Aladin Akyurek]

Hmm, tried that but was getting the typo warning and was correcting it, but then just #value

so i tried it that way, and tried it without the "250". so ...={"200"})*left(k2:k500,4)< >"2000"))

and it was detecting a typo and fixing it to ...)<"2000") with a #value error?

Control+shift+enter, not just enter:

=SUM(IF(H2:H500="Job 1",IF(ISNUMBER(MATCH(IF(MID(K2:K500,4,1)="0","###",LEFT(K2:K500,3)),{"200","250"},0)),1)))

 

[PlumGr8]

tried ctrl,shift,enter on the previous formula, will try yours as well. I'd like to get the other one to work, only to keep it consistent with the other formulas in the cells.

 

[Aladin Akyurek]

tried ctrl,shift,enter on the previous formula, will try yours as well. I'd like to get the other one to work, only to keep it consistent with the other formulas in the cells.

How do you mean? An array formula is not consistent with other formulas?

 

[PlumGr8]

I did get yours to work, i was just originally trying to get this one to work from the other post

=SUMPRODUCT((H2:H500="Job 1")*(LEFT(K2:K500,3)={"200"})*LEFT(K2:K500,4)<>"2000"))

I liked that one mostly because it was similar logic to the others i have now, to make it easier for people to fix in the future if needed. I just couldn't get it to work properly at the time

 

[Aladin Akyurek]

I did get yours to work, i was just originally trying to get this one to work from the other post

=SUMPRODUCT((H2:H500="Job 1")*(LEFT(K2:K500,3)={"200"})*LEFT(K2:K500,4)<>"2000"))

I liked that one mostly because it was similar logic to the others i have now, to make it easier for people to fix in the future if needed. I just couldn't get it to work properly at the time

Does this work? Try on:

Book1
	H	I	J	K
2	Job 1			200a
3	Job 1			200/a
4	Job 1			200
5	Job 1			2000
6	Job 1			250
Sheet1

 

[DanteAmor]

This formula works for me.


=SUMPRODUCT((H2:H500="Job 1")*(LEFT(K2:K500,3)={"200";"250"})*LEFT(K2:K500,4)<>"2000"))

The point is that you do not want this formula, so which formula do you want?

 

[PlumGr8]

No that one i do like and would prefer, only because it is consistent with my other ones. But the other one suggested does work.

This one, i tried again and i get the "typo" error and when corrected it defaults to #value . Just not sure why. I will try to play with it again. I really appreciate all the help from both of you especially. Its really helping me on getting these forms updated etc.

 

[DanteAmor]

What are the others?

 

[PlumGr8]

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