+ Reply to Thread
Results 1 to 4 of 4

Using sumifs to sum multiple criteria, when the criteria changes down a column

  1. #1
    Registered User
    Join Date
    10-02-2019
    Location
    NZ
    MS-Off Ver
    1908
    Posts
    2

    Using sumifs to sum multiple criteria, when the criteria changes down a column

    Hi team ,

    Hoping for some help!

    Looking for a way to sum data using sumifs ( maybe) when you have multiple criteria, but the criteria changes through the data set. Its easy to write the formula for criteria that stays constant, but is there an easier way than manually changing the criteria ?

    My example in the attachment shows A- D , each contain a number of colours.



    To Sum say A and white =sumifs(Column C, Column A, "A",Column B , "White) . Would use cell references for the criteria range usually , but have just used the columns in this example.

    But as I go down the list I want sums for A & Red, A & Blue, B & White, B & Red etc.

    What is the best way to do this please.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,079

    Re: Using sumifs to sum multiple criteria, when the criteria changes down a column

    Try this =SUMIFS($C$1:C2,$A$1:A2,A2,$B$1:B2,B2)
    Or for a better view,=IF(AND(A3=A2,B3=B2),SUMIFS($C$1:C3,$A$1:A3,A3,$B$1:B3,B3),"")
    Click the * to say thanks.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,335

    Re: Using sumifs to sum multiple criteria, when the criteria changes down a column

    The simplest way, which avoids formula altogether is a Pivot Table and a couple fo Slicers to filter your data.

    Using a formula it would be easier to add a helper column to your table and use a SUMIFS. I've shown a couple of examples

    See attached
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    10-02-2019
    Location
    NZ
    MS-Off Ver
    1908
    Posts
    2

    Re: Using sumifs to sum multiple criteria, when the criteria changes down a column

    Cheers Richard,

    Works great. Appreciate your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SUMIFS Multiple Criteria Same Column
    By jsharrard in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-18-2017, 05:08 PM
  2. Sumifs with multiple criteria and criteria in same column
    By Kathryn012 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-17-2017, 07:48 AM
  3. How to sumifs multiple criteria from one column in a VBA code
    By SBBmaster09 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-19-2016, 06:12 AM
  4. [SOLVED] Trying to sumifs with multiple criteria in the same column
    By Rokn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-22-2016, 09:48 PM
  5. [SOLVED] Sumifs to check multiple criteria in the same column
    By fcheng15 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-08-2015, 09:16 AM
  6. sumifs with multiple criteria in one column.
    By apolloni in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-10-2014, 05:13 AM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 PM

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