+ Reply to Thread
Results 1 to 4 of 4

Summing multiple values if various columns conform

  1. #1
    Registered User
    Join Date
    07-17-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Summing multiple values if various columns conform

    Hi guys, just wondering if anyone can help me.

    I'd like to add up the values in a certain column but only when data in the row conforms to certain criteria.

    As an example, if the value in column A can be England, Scotland, Ireland or Wales, the value in column B can be Yes, No or Maybe, and the value in column C can be any number, how would I find the sum of every figure in column C that had Scotland and Yes as the values in it's row.

    I hope this is clear and thanks for any help.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Summing multiple values if various columns conform

    In 2007 you can use SUMIFS function (not available in earlier versions)

    =SUMIFS(C1:C100,A1:A100,"Scotland",B1:B100,"Yes")

    Pre XL2007

    =SUMPRODUCT(--(A1:A100="Scotland"),--(B1:B100="Yes"),C1:C100)

    alter ranges to suit - with SUMPRODUCT esp. important to keep to a minimum (for sake of efficiency)

    (also might be worth considering Pivot Tables)

  3. #3
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Summing multiple values if various columns conform

    Or please see attacheement.
    You can change the value in column B using data Validation
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-17-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Summing multiple values if various columns conform

    Thank you very much, the SUMIFS function was exactly what I was after.

    Thank you very much DeWilk that table will come in very handy.

+ 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