sum if problem, trying to find best solution

[shalombi]

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

 

[Ardus Petus]

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

 

[Arvi Laanemets]

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)

 

[shalombi]

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

 

[Ardus Petus]

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

Cheers,

 

[shalombi]

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

Ma