Say you build up a list of lookup codes in F1:F4

Your SUMIF formula becomes:

=SUMPRODUCT(--ISNUMBER(MATCH(E1:E639,$F$1:$F$4,0)),H1:H639)

HTH
--
AP

"shalombi" <shalombi.28o0s1_1149067813.2194@excelforum-nospam.com> a écrit
dans le message de news:
shalombi.28o0s1_1149067813.2194@excelforum-nospam.com...
>
> I have been asked to do some accountancy monthly and im just trying to
> improve the way I do it, im quite sure im using excel in the worst
> possible way, but I have googled many times for this and couldnt find
> an answer.
>
> I have 2 collumns in my excell sheet one is a reference number and one
> is a price.
>
> The reference number is used to reference what type of payment is made
> and as to be separated for tax problem.
>
> For example 634 and 637 are for drinks while 45 and 450 are for
> treatments.
>
> so i used sum if here is an example:
>
> =SUMIF(E1:E639,"65",H1:H639)+SUMIF(E1:E639,"641",H1:H639)+SUMIF(E1:E639,"313",H1:H639)+SUMIF(E1:E639,"412",H1:H639)+SUMIF(E1:E639,"646",H1:H639)+SUMIF(E1:E639,"306",H1:H639)
>
> Naturally there are a big ammount of numbers, and the range keeps
> changing every month, adapting such a formula is a pain, i was
> wondering a couple of things.
>
> can i not specify a sum if with multiple condition arguments instead of
> repeating sum if every time.
>
> Can i not specify a range of values for sum if, altough it wouldnt work
> for all some are consequent numbers would reduce work and size of
> formulas.
>
> Should i maybe put the actual working out in another sheet adjacent to
> it, i dont really know how to do this but i could read up on it, im
> looking for advice first.
>
> Anything else you see of relevance would be very much apreciated.
>
> A formula with only one range parameter would be nice would make only
> having to change one number.
>
>
> Thank your for reading and maybe for your help.
> Max
>
>
> --
> shalombi
> ------------------------------------------------------------------------
> shalombi's Profile:
> http://www.excelforum.com/member.php...o&userid=34967
> View this thread: http://www.excelforum.com/showthread...hreadid=547037
>