+ Reply to Thread
Results 1 to 6 of 6

SUMIF using 2 Criteria in 2 columns

  1. #1
    Registered User
    Join Date
    05-15-2006
    Posts
    8

    SUMIF using 2 Criteria in 2 columns

    I would be grateful if someone could offer their advice on how to solve a problem I am having with XL.

    I am trying to sum all the values in column C if column A and Column B meet the correct criteria.

    If Column A = X and Column B = B then Sum all the corresponding values in Column C.

    Column A Column B Column C
    X B 1
    Y B 7
    Z C 2
    X B 3
    Y C 10
    (I.e. Answer = 4)

    My Excel book quotes the formula below:
    I.e. {SUM((A:A="X")*(B:B="B)*(C:C))}

    This formula does not look at the whole Column A only the cell in the same row as the formula.

    How can I get the formula to look at the entire array of data and return the sum of all the column C items that meet the correct conditions in Columns A &B?

  2. #2
    Bob Phillips
    Guest

    Re: SUMIF using 2 Criteria in 2 columns

    =SUMPRODUCT(--(A1:A200="X"),--(B1:B200="B"),C1:C200)

    Note that SUMPRODUCT doesn't work with complete columns, you have to specify
    a range.

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "quailc" <quailc.27ukd1_1147693502.8303@excelforum-nospam.com> wrote in
    message news:quailc.27ukd1_1147693502.8303@excelforum-nospam.com...
    >
    > I would be grateful if someone could offer their advice on how to solve
    > a problem I am having with XL.
    >
    > I am trying to sum all the values in column C if column A and Column B
    > meet the correct criteria.
    >
    > If Column A = X and Column B = B then Sum all the corresponding values
    > in Column C.
    >
    > Column A Column B Column C
    > X B 1
    > Y B 7
    > Z C 2
    > X B 3
    > Y C 10
    > (I.e. Answer = 4)
    >
    > My Excel book quotes the formula below:
    > I.e. {SUM((A:A="X")*(B:B="B)*(C:C))}
    >
    > This formula does not look at the whole Column A only the cell in the
    > same row as the formula.
    >
    > How can I get the formula to look at the entire array of data and
    > return the sum of all the column C items that meet the correct
    > conditions in Columns A &B?
    >
    >
    > --
    > quailc
    > ------------------------------------------------------------------------
    > quailc's Profile:

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




  3. #3
    Registered User
    Join Date
    05-15-2006
    Posts
    8
    Bob,

    Many Thanks for your advice with this. The SUMPRODUCT function works perfectly! You have saved me an awful lot of time messing around with this.

    Kind regards
    Corinne

  4. #4
    Registered User
    Join Date
    05-15-2006
    Posts
    8
    I would be grateful if someone could offer their advice on how to solve a problem I am having with XL.

    Is it possible to perform the SUMPRODUCT function on a data range that is in a seperate workbook?

    I have tried selecting the range from another workbook but it comes back with #value result.

    How do I link the 2 workbooks together for this to work correctly?

  5. #5
    Bob Phillips
    Guest

    Re: SUMIF using 2 Criteria in 2 columns

    Yes you can. Show us what you have tried.

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "quailc" <quailc.27uoj0_1147698903.2754@excelforum-nospam.com> wrote in
    message news:quailc.27uoj0_1147698903.2754@excelforum-nospam.com...
    >
    > I would be grateful if someone could offer their advice on how to solve
    > a problem I am having with XL.
    >
    > Is it possible to perform the SUMPRODUCT function on a data range that
    > is in a seperate workbook?
    >
    > I have tried selecting the range from another workbook but it comes
    > back with #value result.
    >
    > How do I link the 2 workbooks together for this to work correctly?
    >
    >
    > --
    > quailc
    > ------------------------------------------------------------------------
    > quailc's Profile:

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




  6. #6
    Registered User
    Join Date
    05-15-2006
    Posts
    8
    I am trying to link my current workbook with:
    Workbook name: Leave Planner Apr 06 - Nov 06 working.xls
    Worksheet: May 06

    (They are saved in the same folder)


    =(SUMPRODUCT(1*('[Leave Planner Apr 06 - Nov 06 working.xls]May 06 '!$A$41:$A$68="A&C Cert"),1*('[Leave Planner Apr 06 - Nov 06 working.xls]May 06 '!$D$40="E")*'[Leave Planner Apr 06 - Nov 06 working.xls]May 06 '!$D$41:$D$68))

    Thank you
    Corinne
    Last edited by quailc; 05-15-2006 at 10:04 AM.

+ 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