+ Reply to Thread
Results 1 to 87 of 87

SUMIF based on two conditions

  1. #1
    Registered User
    Join Date
    01-03-2005
    Posts
    35

    SUMIF based on two conditions

    I have a list of data, which goes something like this

    Month Type Amount
    Jan A 10
    Jan A 15
    Feb B 10
    Jan B 5
    March A 20

    etc

    Now, I have a second sheet, which I want to go something like this..

    Jan

    Type A TOTAL
    Type B TOTAL

    Feb

    Type A TOTAL
    Type B TOTAL

    Mar

    Type A TOTAL
    Type B TOTAL

    In order to do this I need to have a formula which says

    "SUMIF (Month=Jan and Type=A)"

    Is there anyway to do this?

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Good afternoon Grey

    There is a way to do this but we'll have to utilise arrays to do so. The example below assumes that your data containing conditions is in column A and B and your data to sum is in column C. My assumtion is that your data covers 50 rows.

    =SUM(IF($A$1:$A$50="January",IF($B$1:$B$50="a",$C$1:$C$50,0),0))

    As it's an array formula don't forget to use Ctrl + Shift + Enter to commit it (not just Enter on it's own).

    HTH

    DominicB

  3. #3
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  4. #4
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  5. #5
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  6. #6
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  7. #7
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  8. #8
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  9. #9
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  10. #10
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  11. #11
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  12. #12
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  13. #13
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  14. #14
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  15. #15
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  16. #16
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  17. #17
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  18. #18
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  19. #19
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  20. #20
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  21. #21
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  22. #22
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  23. #23
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  24. #24
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  25. #25
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  26. #26
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  27. #27
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  28. #28
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  29. #29
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  30. #30
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  31. #31
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  32. #32
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  33. #33
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  34. #34
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  35. #35
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  36. #36
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  37. #37
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  38. #38
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  39. #39
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  40. #40
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  41. #41
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  42. #42
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  43. #43
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  44. #44
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  45. #45
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  46. #46
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  47. #47
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  48. #48
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  49. #49
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  50. #50
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  51. #51
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  52. #52
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  53. #53
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  54. #54
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  55. #55
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  56. #56
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  57. #57
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  58. #58
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  59. #59
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  60. #60
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  61. #61
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  62. #62
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  63. #63
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  64. #64
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  65. #65
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  66. #66
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  67. #67
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  68. #68
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  69. #69
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  70. #70
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  71. #71
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  72. #72
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  73. #73
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  74. #74
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  75. #75
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  76. #76
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  77. #77
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  78. #78
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  79. #79
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  80. #80
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  81. #81
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  82. #82
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  83. #83
    Bob Phillips
    Guest

    Re: SUMIF based on two conditions

    =SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  84. #84
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Yes. Use an "array" formula.

    A B C

    1 | Month Type Amount
    2 | Jan A 10
    3 | Jan A 15
    4 | Feb B 10
    5 | Jan B 5
    6 | Mar A 20

    Jan
    Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
    Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

    This is an example of an "array" formula.
    To create the array, you enter the formula as above and then
    hold down the CTRL and SHIFT keys as you press the ENTER key.

    There is a nice tutorial on array functions at:
    http://www.cpearson.com/excel/array.htm

    Regards,
    Andrew


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

  85. #85
    Duke Carey
    Guest

    RE: SUMIF based on two conditions

    Maybe a pivot table would be faster & easier

    "grey" wrote:

    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
    > View this thread: http://www.excelforum.com/showthread...hreadid=391795
    >
    >


  86. #86
    Anne Troy
    Guest

    Re: SUMIF based on two conditions

    Hi, grey. I would probably use Subtotals instead:
    http://www.officearticles.com/excel/...soft_excel.htm

    But to answer your question, try the instructions here:
    http://www.officearticles.com/excel/...soft_excel.htm

    *******************
    ~Anne Troy

    www.OfficeArticles.com



    "grey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a list of data, which goes something like this
    >
    > Month Type Amount
    > Jan A 10
    > Jan A 15
    > Feb B 10
    > Jan B 5
    > March A 20
    >
    > etc
    >
    > Now, I have a second sheet, which I want to go something like this..
    >
    > Jan
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Feb
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > Mar
    >
    > Type A TOTAL
    > Type B TOTAL
    >
    > In order to do this I need to have a formula which says
    >
    > "SUMIF (Month=Jan and Type=A)"
    >
    > Is there anyway to do this?
    >
    >
    > --
    > grey
    > ------------------------------------------------------------------------
    > grey's Profile:

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




  87. #87
    Andrew L via OfficeKB.com
    Guest

    Re: SUMIF based on two conditions


    Of course the example I gave showed the return area on the same page as the
    data, but there is no reason you can't do the same think on a different page.
    The formula would just contain the sheet reference. It would look something
    like:
    {=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200508/1

+ 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