sum if problem, trying to find best solution

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

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
>

Hi

1. It depends on how well are your reference numbers planned. When with a
bit of foresight, then you can use formulas like
=SUMIF(E1:E639,">=45",H1:H639)-SUMIF(E1:E639,"<=450",H1:H639)

2.Use dynamic named ranges. You must have some ID-column, where always is
some non-empty value whenever a row is present in table. (For my example,
let this column be A, and your table on sheet MySheet, without any header
row). Define names:
Ref=OFFSET(MySheet!$E$1,,,COUNTA(MySheet!$A:$A),1)
Sum=OFFSET(MySheet!$H$1,,,COUNTA(MySheet!$A:$A),1)

Now the formula above will be
=SUMIF(Ref,">=45",Sum)-SUMIF(Ref,"<=450",Sum)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Ok so in my case i have 4 different types in need to separate in, so if i make lists in separate collumns like you said F1:f4 for one thing and G1:G5 in another i can reference to them that way?

Well ill try what you said, thank you for the prompt reply anyway.

Max

Yes you can make different lists of variable length, then apply my formula
changing $F1:$F4 for whatever the new list is.

Cheers,
--
AP

"shalombi" <shalombi.28o33a_1149070800.5468@excelforum-nospam.com> a écrit
dans le message de news:
shalombi.28o33a_1149070800.5468@excelforum-nospam.com...
>
> Ok so in my case i have 4 different types in need to separate in, so if
> i make lists in separate collumns like you said F1:f4 for one thing and
> G1:G5 in another i can reference to them that way?
>
> Well ill try what you said, thank you for the prompt reply anyway.
>
> Max
>
>
> --
> shalombi
> ------------------------------------------------------------------------
> shalombi's Profile:
> http://www.excelforum.com/member.php...o&userid=34967
> View this thread: http://www.excelforum.com/showthread...hreadid=547037
>

Thanks alot ive been able to mae my 5 different types grid and now its all nice and clean, I owe you guys.

Max