+ Reply to Thread
Results 1 to 5 of 5

sumif and range validation

  1. #1
    Registered User
    Join Date
    07-19-2006
    Location
    Jamaica
    Posts
    85

    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

  2. #2
    Peo Sjoblom
    Guest

    Re: sumif and range validation

    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" <c991257.2cwp2u_1156191012.5927@excelforum-nospam.com> wrote in
    message news:c991257.2cwp2u_1156191012.5927@excelforum-nospam.com...
    >
    > 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
    >




  3. #3
    Registered User
    Join Date
    07-19-2006
    Location
    Jamaica
    Posts
    85
    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?
    --
    M?ller

  4. #4
    Peo Sjoblom
    Guest

    Re: sumif and range validation

    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" <c991257.2cy864_1156262416.108@excelforum-nospam.com> wrote in
    message news:c991257.2cy864_1156262416.108@excelforum-nospam.com...
    >
    > 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
    >




  5. #5
    Registered User
    Join Date
    07-19-2006
    Location
    Jamaica
    Posts
    85
    Actually SUMIF works in my Excel 2000,
    but SUMIFS would be very very nice.

    I'll try my luck with the sumproduct for now. What does the -- in the formula do?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1