count if the number is greater than 0

[sharky1978]

hi all could you help please

I am using the following formula to count various things , like the weather, if a truck is on contract,

=SUMPRODUCT(('SUPERVISORS INPUT 2021'!O46:$O$2000='SUPERVISORS INPUT 2021'!O46)*('SUPERVISORS INPUT 2021'!X46:$AD$2000="WEATHER"))

how would I get the formula above to count anything greater than 0

hope that makes sense

[AliGW]

Maybe:

=SUMPRODUCT(('SUPERVISORS INPUT 2021'!O46:$O$2000='SUPERVISORS INPUT 2021'!O46)*('SUPERVISORS INPUT 2021'!X46:$AD$2000="WEATHER")*(Range>0))

[sharky1978]

thanks ali

can I replace weather with range as i only want to count anything >0 in a cell

[sharky1978]

I just get a name ref error when I do the above

=SUMPRODUCT(('SUPERVISORS INPUT 2021'!O46:$O$2000='SUPERVISORS INPUT 2021'!O46)*('SUPERVISORS INPUT 2021'!X46:$AD$2000="WEATHER"))

I was going to post a spread sheet but its just messy,

to explain what my formula is doing its searching down my Colum for the vehicle registration and is counting various issue with the wagon which has stopped it from carrying out its work, the only thing I'm unable to do is use the above formula to count how many times my vehicle worked,

so anything above 0 next to the registration will give me a count of 1, so if i had 5 on Monday and 0.2 on Tuesday my machine returns a count of worked days = 2 and the machine lost a day due to weather i would have

worked days = 2
down days for weather = 1

hope that makes what I'm after a little clearer

[AliGW]

Attach a sample workbook. There are instructions at the top of the page.

[sharky1978]

hi sorry it took so long to get back

i have attached a sample work book for you to have a look at (I HOPE)

[CARACALLA]

Why for V15 VPL 7 not 8 ?

[sharky1978]

there number plates, some of the plates weren't available

[sharky1978]

sorry i misunderstood

v15 wouldn't be 7 or 8 as this would be how much material the machine laid during the day, so it would have worked Monday, Tuesday Wednesday and done nothing for the rest of the week so i would like to capture the 3 days it worked laying materials

this would be over 52 weeks of the year

thanks

[Glenn Kennedy]

You have provided no manually calculated expected answers... so this is a guess.

=SUMPRODUCT(--(Sheet1!$L$2:$L$31=A2),Sheet1!$U$2:$U$31)

copied down.

[sharky1978]

sorry i will update and repost

[sharky1978]

hope this helps just added the count to sheet 2 from list 1

[CARACALLA]

L2=SUMPRODUCT((Sheet1!$L$2:$L$31=[@VEHICLE])*(ISNUMBER(Sheet1!$U$2:$AA$31)))

Copy down

[Glenn Kennedy]

Caracalla, that'll be it. I was confused by this:

I WOULD LIKE DAYS USED ON CONTRACT TO COUNT ANY DAY THAT A FIGURE IS INPUTED ON SHEET 1 CELLS U2 : U31

[CARACALLA]

We await the response of sharky 78

[sharky1978]

hi m8 works perfect

(no idea how it does it but it does it)

excel is whitch craft to me

so my final formula ended up =SUMPRODUCT(('SUPERVISORS INPUT 2021'!$O$46:$O$2000=[@VEHICLE])*(ISNUMBER('SUPERVISORS INPUT 2021'!$X$46:$AD$2000)))

thanks for the help