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 much we are owed.

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

received-not banked yet

banked-not cleared yet

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

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

incidentally, unpaid, received-not banked yet, and banked-not cleared yet, are all in the same column. i just tick a drop down box to select the appropriate option. i don't know if this is relevant or not but i 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))


--
HTH

Bob Phillips

"buachille" <[email protected]> wrote
in message news:[email protected]...
>
> 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 much
> we are owed.
>
> I now want the formula to add up all the boxes with unpaid next to
> them, plus all boxes with these phrases next to them:
>
> received-not banked yet
>
> banked-not cleared yet
>
> so i have tried things like this
> =SUMIF(G126:G242,"UNPAID(or)received-not banked yet(or)banked-not
> cleared yet",F126:F242)
>
> but it does not work. as you will have guessed i am not too great with
> excel.
>
> incidentally, unpaid, received-not banked yet, and banked-not cleared
> yet, are all in the same column. i just tick a drop down box to select
> the appropriate option. i don't know if this is relevant or not but i
> thought i'd better mention it.
>
>
> can anybody tell me how i do this. i would appreciate it very much.
>
> thanks.
>
>
> --
> buachille
> ------------------------------------------------------------------------
> buachille's Profile:
http://www.excelforum.com/member.php...o&userid=19523
> View this thread: http://www.excelforum.com/showthread...hreadid=398502
>

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

--
HTH

Bob Phillips

"buachille" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> buachille
> ------------------------------------------------------------------------
> buachille's Profile:
http://www.excelforum.com/member.php...o&userid=19523
> View this thread: http://www.excelforum.com/showthread...hreadid=398502
>