sum if formula help

[Harvey Waxman]

Three columns, A,B,C.

I'd like to know the difference between the total of columns A and B only when the corresponding value in C is blank.

Thanks

 

[John_McGhie_[MVP]]

What do you mean by "corresponding value"?

Is this what you want?

=IF(SUM(C1:C25)=0,A26-B26,SUM(C1:C25))

Cheers

Three columns, A,B,C.

I'd like to know the difference between the total of columns A and B only when
the corresponding value in C is blank.

Thanks

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[Harvey Waxman]

What do you mean by "corresponding value"?

Is this what you want?

=IF(SUM(C1:C25)=0,A26-B26,SUM(C1:C25))

Cheers

Sorry for being muddy. If the value in C is blank I'd like to subtract the corresponding value in B from that in A
for the entire column

A B C
20 40 Y
30 30 0
50 20 30
22 18 Y
66 30 36
55 43 12
22 3 9

Answer = 87

Thanks

 

[pjonesCET]

=IF(C1=0,A1-B1)

Problem is if anything is other than zero Column D1 shows False. Now if you can figure out how to cause False tho nots how you'll be all set)

I did one column you'll have to repeat the formula In Column D for each row. If you do a Blanket item that taken in all the columns then if any column was 0 then it would sum all the Column A&B items.

 

[pjonesCET]

Correct the formula

its IF(C1="",A1-B1)

if anything typed in C1 the the subtraction happens and is placed in Cell D1.

Sorry The other way 0 has to be in C1 for D1 to preform sum

 

[Harvey Waxman]

What do you mean by "corresponding value"?

Is this what you want?

=IF(SUM(C1:C25)=0,A26-B26,SUM(C1:C25))

Cheers

This is what I finally came up with which appears to give the right results:

=SUM(IF(C18:C11000="",(A18:A11000)-(B18:B11000)))

But shouldn't this be the same? It isn't. I can't see why.

=IF(C18:C11000="",SUM(A18:A11000)-SUM(B18:B11000))

 

[Harvey Waxman]

This is what I finally came up with which appears to give the right results:

=SUM(IF(C18:C11000="",(A18:A11000)-(B18:B11000)))

But shouldn't this be the same? It isn't. I can't see why.

=IF(C18:C11000="",SUM(A18:A11000)-SUM(B18:B11000))

I was premature. Neither gives the right results.


I'll need to do some more work. My requirements are actually more complex in that there are
two columns of conditions that I want to meet before I do the subtraction.

I'll try some more and get back.

 

[Harvey Waxman]

Solved:

{=SUM(IF((M18:M11000>0%)*(C18:C11000=""),(A18:A11000)-(B18:B11000)))}

Thanks to all. Two conditions to meet, then subtract total column A from total column B.