i need to add more options to a formula but i don't know how

[buachille]

hi,

on one of my worksheets i use this formula

=SUMIF(G126:G242,"UNPAID",F126:F242)

this tells excel to add up all our unpaid invoices and tell me how muc
we are owed.

I now want the formula to add up all the boxes with unpaid next t
them, plus all boxes with these phrases next to them:

received-not banked yet

banked-not cleared yet

so i have tried things like thi
=SUMIF(G126:G242,"UNPAID(or)received-not banked yet(or)banked-no
cleared yet",F126:F242)

but it does not work. as you will have guessed i am not too great wit
excel.

incidentally, unpaid, received-not banked yet, and banked-not cleare
yet, are all in the same column. i just tick a drop down box to selec
the appropriate option. i don't know if this is relevant or not but
thought i'd better mention it.


can anybody tell me how i do this. i would appreciate it very much.

thanks

 

[Bob Phillips]

=SUMPRODUCT((G126:G242={"UNPAID","received-not banked yet","banked-not
cleared yet"})*(F126:F242))

 

[buachille]

Bob,

Thankyou very much. It works perfectly.

What is that sumproduct command. I could sort of understand the if
command but i've never heard of that one before.

Where could i get some good tips on writing formulas. I have a book on
excel but it is baffling, and i searched google quite a bit to try to
find out how to write the formula that you have just provided, but i
failed to find easy instructions.

I'm sure there must be a good guide somewhere - i hope.

Anyway, many thanks for your help.

 

[Bob Phillips]

Hi buachille,

You could start at http://www.xldynamic.com/source/xld.SUMPRODUCT.html for
SUMPRODUCT.

I guess the best place to start with formulas is John Walkenbach's Excel
2003 Formulas. I may have got the title wrong, but it will have those words
in it.