**Question description:**

*This user has given permission to use the problem statement for this blog.*

Need help with excel formula. I want to minus 4 if the cell is greater than 0

Solved by K. Y. in 17 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
11/12/2017 - 06:52

HI, It's pleasure to help you today:)

User
11/12/2017 - 06:52

Hello.

Excelchat Expert
11/12/2017 - 06:52

Please share me sample data and formula.

Excelchat Expert
11/12/2017 - 06:53

Ok. Which cell i need to address in the preview?

User
11/12/2017 - 06:53

I need help with H19

Excelchat Expert
11/12/2017 - 06:54

Ok. What is the cell i need to refer for sum value?

User
11/12/2017 - 06:54

This is to monitor how many days someone is working a week (mon-sun). They should only be working 3 days per week. If they work over this, i want a formula to calculate the number of days they need to not work in the future to make this back to zero

Excelchat Expert
11/12/2017 - 06:56

I want to minus 4 if the cell is greater than 0? What cell i need to check for >0?

User
11/12/2017 - 06:57

It would be the number of 'G's between mon-sun

User
11/12/2017 - 06:57

might need another call to calc these

User
11/12/2017 - 06:58

Okay so

User
11/12/2017 - 06:58

A4 calculates how many Gs over 3 they have worked

User
11/12/2017 - 06:58

if this equals 1 or more, i want I20 to calculate them

Excelchat Expert
11/12/2017 - 06:58

ok.

User
11/12/2017 - 06:59

them being A4, A11 & A18

User
11/12/2017 - 06:59

In my real spreadsheet the dates go on until 2019

Excelchat Expert
11/12/2017 - 07:02

I have updated it. Please check.

Excelchat Expert
11/12/2017 - 07:02

=if(COUNTIF(D4:D10,"G")-3>0,COUNTIF(D4:D10,"G")-3,0)

Excelchat Expert
11/12/2017 - 07:02

The formula is written in A4 cell.

User
11/12/2017 - 07:02

i would epect I 20 to equal -2

User
11/12/2017 - 07:02

because she didn;t work the full 3 days

User
11/12/2017 - 07:02

she only had 1 G in the range

User
11/12/2017 - 07:02

so it should flag 2 days are not worked

Excelchat Expert
11/12/2017 - 07:03

ok

Excelchat Expert
11/12/2017 - 07:03

Then what is the condition to include >0?

Excelchat Expert
11/12/2017 - 07:03

I can change the formula to show -2

User
11/12/2017 - 07:03

equally, if she worked 4 days, i'd expect it to equal 1

Excelchat Expert
11/12/2017 - 07:03

Please check now.

User
11/12/2017 - 07:03

the reason why i want it to only count if over 0 is because

User
11/12/2017 - 07:04

at the moment, because she has not worked future dates, the cells are equalling - 3 all the way down to 2019

User
11/12/2017 - 07:04

which is should I20 as like -150

User
11/12/2017 - 07:05

but instead I am thinking if at least one day is worked, then that should trigger I20 to include it in its count

User
11/12/2017 - 07:05

opposed to counting all the -3's

User
11/12/2017 - 07:05

sorry it is hard to explain

Excelchat Expert
11/12/2017 - 07:05

I understood. I am working on it.

User
11/12/2017 - 07:06

Can you see the yellows all show -3 because she has not stated which dates she actually worked therefore I20 shows incorrect result

Excelchat Expert
11/12/2017 - 07:06

I have changed it. Please check it now.

Excelchat Expert
11/12/2017 - 07:06

=if(COUNTIF(D4:D10,"G")>0,COUNTIF(D4:D10,"G")-3,0)

Excelchat Expert
11/12/2017 - 07:06

Here is the formula. When the count is >0 then only it deduct 3, else, it stays 0.

User
11/12/2017 - 07:07

hm is that right

User
11/12/2017 - 07:07

1 sec

Excelchat Expert
11/12/2017 - 07:07

Thank you.

User
11/12/2017 - 07:08

yes it is

User
11/12/2017 - 07:08

you are very clever!

Excelchat Expert
11/12/2017 - 07:08

Have a great day ahead:). Please visit Got It Pro for new queries.

User
11/12/2017 - 07:08

thank you :)

Excelchat Expert
11/12/2017 - 07:08

Please leave a great feedback after ending the session.

User
11/12/2017 - 07:09

will do

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*