Querying cells across cells

[Kembo]

Hi all,
I'm wanting to create a basic kind of calculator for
tracking my finances.
What I am wanting to do (and apologies if it's really
simple, but I'm just missing it), is :

I have 4 columns one of which is named : Payments

This column is set to calculate the total at the end of
the file.

I also have another column named 'Paid?'

What I'm looking to do is find a formulae that will add
the payments column together (easy enough), but when I add
the word 'Yes' into the Paid column, that amount is
deducted from the total at the end.

Does that make sense?

Basically :if a word is type into the cell of one column,
can that be made to affect the sum of another column?

Phew...

 

[Frank Kabel]

Hi
try something like the following (column A is 'Payments' and column B
'Paid')
=SUMIF(B1:B100,"<>Yes",A1:A100)

 

[Guest]

Thanks Frank,
That works a treat.

-----Original Message-----
Hi
try something like the following (column A is 'Payments' and column B
'Paid')
=SUMIF(B1:B100,"<>Yes",A1:A100)

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[kkknie]

Use the SUMPRODUCT function:

=SUMPRODUCT(C2:C6,--(D26<>"Yes"))

Substitute your Payments range for C2:C6.
Substitute your Paid? range for D26.

K

Edit: Frank's response seems quite less complicated..