sumif and range validation

Hi.

I need to sum some numbers and all these are linked to accounts.

Lets say I have accounts in column a:

4001
4002
...
4998
4999

and I want to sum values from column b in groups ranging from eg. 4230-4239, where the boundries of these ranges are defined elsewhere.

The accounts are ussually ground in 10s, i.e. from 4230 to 4239 is related to the same "thing".

=sumif(col a, Rangeproblem, col b)

How do I solve the range problem?

/Møller

A guess

=SUMIF(A:A,">=4230",B:B)-SUMIF(A:A,">4239",B:B)


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"c991257" <[email protected]> wrote in
message news:[email protected]...
>
> Hi.
>
> I need to sum some numbers and all these are linked to accounts.
>
> Lets say I have accounts in column a:
>
> 4001
> 4002
> ..
> 4998
> 4999
>
> and I want to sum values from column b in groups ranging from eg.
> 4230-4239, where the boundries of these ranges are defined elsewhere.
>
> The accounts are ussually ground in 10s, i.e. from 4230 to 4239 is
> related to the same "thing".
>
> =sumif(col a, Rangeproblem, col b)
>
> How do I solve the range problem?
>
> /Møller
>
>
> --
> c991257
> ------------------------------------------------------------------------
> c991257's Profile:
> http://www.excelforum.com/member.php...o&userid=36549
> View this thread: http://www.excelforum.com/showthread...hreadid=573914
>

You can't do that with SUMIF unless you use 2007 and then there is a new
function called SUMIFS, nevertheless you can use

=SUMPRODUCT(--(A1:A10000>=C1),--(A1:A10000<=C2),B1:B10000)

note that you cannot use the whole column so you need to specify the range


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"c991257" <[email protected]> wrote in
message news:[email protected]...
>
> That would be a sloution, but I would prefer to take the range ends from
> two separate cells. Something like
> =sumif(A:A, and(A:A>=C1,A:A<=C2), B:B)
>
> any ideas?
>
>
> --
> c991257
>
>
> ------------------------------------------------------------------------
> c991257's Profile:
> http://www.excelforum.com/member.php...o&userid=36549
> View this thread: http://www.excelforum.com/showthread...hreadid=573914
>