MS Excel question

[niall_heavey]

Hi all

I'm looking to do what as far as I know is a straight forward enough task in excel...............just can't think the best way of doing it!!

I am taking registrations for a course and I have two columns (K&L) for paid and fee
Column L is the fee that the person is due to pay and at the bottom of the list the total amount taken in is added together to give me the "Monies in (Expected)" total

However what I am trying to do in row K is make a total of "Monies in (paid)"
To do this I need a formula that will see text in a cell and then take the value of the cell beside it to add to the total............ for example:
Cell L1 = €50, L2 = 55, L3 = 60
The total for monies in expected will be €165, but if only K1 and K3 have "Paid" in the cell it means that the money already paid will only be €110

So if any Cell in column K has text "Paid" in it the formula should sum all values that are in Column L in the same row

I'm not sure that all makes sense, hopefully it does!

Look forward to your response

Thanks

N

[BitBlt]

The only way I know how to do something like that is with a macro, something like:

Sub AddPaid()
For i = 2 To 4
    If Excel.Cells(i, "M") = "Paid" Then
        myValue = myValue + Excel.Cells(i, "L").Value
    End If
Next i
Excel.Cells(5, "M").Value = myValue
End Sub

but I don't think that's what you mean.

If you want to use up another column (say "N"), you could use something like:

=IF(M2="Paid", L2, "")

on row 2, then copy that to each row of the new column, and then

=SUM(N2:N4)

or however many rows, at the bottom. Again, probably not what you were looking for, but I hope that gives you some ideas.

[qwiktalk]

I think you should be able to accomplish this with a combination of OFFSET and IF functions. Check out the help documentation for those functions to see if it works for you.

If you need to talk through it with an expert, we just launched QwikTalk for Excel Help.

[wilsonkiw]

I was stuck on this too but think I found the answer on tips for excel soemwhere. I used sumif to sum only where a condition is met