Querying cells across cells

K

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...
 
F

Frank Kabel

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

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



.
Click to expand...
 
K

kkknie

Use the SUMPRODUCT function:

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

Substitute your Payments range for C2:C6.
Substitute your Paid? range for D2:D6.

K

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