+ Reply to Thread
Results 1 to 20 of 20

SUMIF with AND for range and criteria

  1. #1
    Registered User
    Join Date
    08-02-2005
    Posts
    2

    SUMIF with AND for range and criteria

    Hi,

    I a bit of an Excel newbie, and can't figure this one out. Maybe it's easy for someone out there?

    I need to be able to do a SUMIF based on 2 seperate columns in order to put a sum of values from one sheet into a summary on another.

    For example,

    Sheet1
    A B C
    John Smith 40
    Joe Bloggs 38
    John Smith 24
    Jane Smith 36

    Sheet2
    X Y Z
    John Smith 64


    So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C, based on BOTH A matching X and B matching Y.


    Thanks for any help you can give.

    Dave.

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    It's not really clever, but I would run two helper columns, a new column C on sheet1 =A1&" "&B1 and a new column Z on sheet2 =X1&" "&Y1
    this would give me a safety check on the match and an easy SumIf.

    and the total column, now in AA, then reads

    =SUMIF(Sheet1!C1:Sheet1!C4,Z1:Z4,Sheet1!D1:D4)

    I presume that you know to formula-drag down the column for the extent of your data.



    Quote Originally Posted by davidm_ba
    Hi,

    I a bit of an Excel newbie, and can't figure this one out. Maybe it's easy for someone out there?

    I need to be able to do a SUMIF based on 2 seperate columns in order to put a sum of values from one sheet into a summary on another.

    For example,

    Sheet1
    A B C
    John Smith 40
    Joe Bloggs 38
    John Smith 24
    Jane Smith 36

    Sheet2
    X Y Z
    John Smith 64


    So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C, based on BOTH A matching X and B matching Y.


    Thanks for any help you can give.

    Dave.

  3. #3
    Registered User
    Join Date
    08-02-2005
    Posts
    2
    Thanks Bryan,

    It doesn't have to be clever, as long as it works, and that seems like a straight-forward way to do it.

    Dave.

  4. #4
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  5. #5
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  6. #6
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  7. #7
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  8. #8
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  9. #9
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  10. #10
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  11. #11
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  12. #12
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  13. #13
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  14. #14
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  15. #15
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  16. #16
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  17. #17
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  18. #18
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  19. #19
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




  20. #20
    Bob Phillips
    Guest

    Re: SUMIF with AND for range and criteria

    =SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

    --
    HTH

    Bob Phillips

    "davidm_ba" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I a bit of an Excel newbie, and can't figure this one out. Maybe it's
    > easy for someone out there?
    >
    > I need to be able to do a SUMIF based on 2 seperate columns in order to
    > put a sum of values from one sheet into a summary on another.
    >
    > For example,
    >
    > Sheet1
    > A B C
    > John Smith 40
    > Joe Bloggs 38
    > John Smith 24
    > Jane Smith 36
    >
    > Sheet2
    > X Y Z
    > John Smith 64
    >
    >
    > So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
    > based on BOTH A matching X and B matching Y.
    >
    >
    > Thanks for any help you can give.
    >
    > Dave.
    >
    >
    > --
    > davidm_ba
    > ------------------------------------------------------------------------
    > davidm_ba's Profile:

    http://www.excelforum.com/member.php...o&userid=25811
    > View this thread: http://www.excelforum.com/showthread...hreadid=392096
    >




+ 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