sumif

[Stclements1]

I have a job sheet that has a list of jobs to be done.
In cell i20 I have the sum of the costs of all those jobs.
In column M is either 1, if the job has been completed or 0 if the job is still ongoing.
I have formatted the sheet so that when there is a 1 in column M then that row is greyed out.
What I want to do is the value in I20 should no longer count the value in any call in column i if the corresponding cell in column M is 1 and the row has been greyed out.

I have tried countif and sum if, but without any joy.
Any help is much appreciated.

 

[Bosquedeguate]

I have a job sheet that has a list of jobs to be done.
In cell i20 I have the sum of the costs of all those jobs.
In column M is either 1, if the job has been completed or 0 if the job is still ongoing.
I have formatted the sheet so that when there is a 1 in column M then that row is greyed out.
What I want to do is the value in I20 should no longer count the value in any call in column i if the corresponding cell in column M is 1 and the row has been greyed out.

I have tried countif and sum if, but without any joy.
Any help is much appreciated.

please provide your formulas and sample data (use a mini-sheet XL2BB if you can)

 

[awoohaw]

Try this:

Mr excel questions 55.xlsm
	H	I				M
1	Job Nmbr	Cost				Completed
2	Job1	68				0
3	Job2	53				0
4	Job3	70				0
5	Job4	71				0
6	Job5	72				0
7	Job6	72				1
8	Job7	62				0
9	Job8	69				0
10	Job9	61				1
11	Job10	61				0
12	Job11	53				0
13	Job12	53				1
14	Job13	62				0
15	Job14	52				1
16	Job15	73				1
17	Job16	55				1
18	Job17	75				1
19	Job18	57				1
20		641
Stclements1

Cell Formulas
Range	Formula
I20	I20	=SUMIF($M$2:$M$19,0,$I$2:$I$19)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:M19	Expression	=$M2=1	text	NO

 

[Stclements1]

Many

Try this:

Mr excel questions 55.xlsm
	H	I				M
1	Job Nmbr	Cost				Completed
2	Job1	68				0
3	Job2	53				0
4	Job3	70				0
5	Job4	71		Try this: Mr excel questions 55.xlsm H I M 1 Job Nmbr Cost Completed 2 Job1 68 0 3 Job2 53 0 4 Job3 70 0 5 Job4 71 0 6 Job5 72 0 7 Job6 72 1 8 Job7 62 0 9 Job8 69 0 10 Job9 61 1 11 Job10 61 0 12 Job11 53 0 13 Job12 53 1 14 Job13 62 0 15 Job14 52 1 16 Job15 73 1 17 Job16 55 1 18 Job17 75 1 19 Job18 57 1 20 641 Stclements1 Cell Formulas Range Formula I20 I20 =SUMIF($M$2:$M$19,0,$I$2:$I$19) Cells with Conditional Formatting Cell Condition Cell Format Stop If True H2:M19 Expression =$M2=1 text NO ra-200|t:fc][XR][XD]H2:M19	Expression	=$M2=1	text	NO
Stclements1

Many thanks your sumif formula worked perfectly

 

[awoohaw]

Many

Many thanks your sumif formula worked perfectly

I'm happy you were able to find a solution here.

Best Wishes!