Final issue using SUM/SUMPRODUCT

[PlumGr8]

So, received a bunch of help in my other thread on how to set up some formulas to only look and count certain values that we use for tracking maintenance performed at our Job Sites. The only issues i have left is when tracking 200 and 2000.

So, i have the one formula that successfully sees when i enter 200

=SUM(IF(H3:H4825="Job Site 1",IF(ISNUMBER(MATCH(IF(MID(K3:K4825,4,1)="0","###",LEFT(K3:K325,3)),{"200"},0)),1)))

and i have one for 2000

=SUMPRODUCT(--(H3:H325="Job Site 1"),--(LEFT(K3:K325,4)="2000"))

The issue i have is when i enter 2000, the current way i HAVE to enter 2000 first, ie "2000hr/batteries", if i type 400/2000 it won't see the 2000 to count/track it. I'll keep playing with it, but if anyone has a solution that would be awesome, thanks

 

[CyrusTheVirus]

You could try this for the SUMPRODUCT formula?

=SUMPRODUCT(--(H3:H325="Job Site 1"),--(ISNUMBER(SEARCH("2000",K3:K325))))

 

[sandy666]

deleted

 

[PlumGr8]

i will give that one a try here in a bit, thank you

 

[PlumGr8]

That worked perfect, was the one piece i was missing from the other thread i had going. Thanks again

 

[CyrusTheVirus]

Glad to hear. You're welcome.