+ Reply to Thread
Results 1 to 8 of 8

solved

  1. #1
    Registered User
    Join Date
    01-18-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    solved

    Hi all,

    I am trying to do a SUMIFS calculation for 5 criterias and having trouble doing so.

    helpsumifs.xlsx

    I wish to make a table as shown in the excel spreadsheet whereby if I change the values in cell B17:B20, I get the corresponding figure in cells C23:C25.

    I am getting #VALUE! each time I try this.

    Any help would be appreciated!

    Thanks,
    Last edited by ravidesai; 01-20-2016 at 03:30 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,422

    Re: SUMIFS help needed - multiple criteria (horizontal and vertical)

    Most likely because the range sizes are different.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    01-18-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    Re: SUMIFS help needed - multiple criteria (horizontal and vertical)

    Thanks for your prompt reply.

    But if I needed it to look up the whole range, is it not possible as it is going across the 12 months with the addition of Q1-Q4?

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFS help needed - multiple criteria (horizontal and vertical)

    All the ranges in Sumifs must be the same dimensions.
    So you can't sum a multi-column range based on single-column criteria ranges.


    Try
    =SUMIFS(INDEX($E$2:$T$13,0,MATCH($B$19,$E$1:$T$1,0)),$A$2:$A$13,B23,$B$2:$B$13,$B$17,$C$2:$C$13,$B$18,$D$2:$D$13,$B$20)

  5. #5
    Registered User
    Join Date
    01-18-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    Re: SUMIFS help needed - multiple criteria (horizontal and vertical)

    That works great! Thanks

    Jonmo1 if you dont mind me asking, could you explain briefly what the index and match functions do?

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFS help needed - multiple criteria (horizontal and vertical)

    You're welcome.

    INDEX($E$2:$T$13,0,MATCH($B$19,$E$1:$T$1,0))

    Match finds which column has the value of B19 in row 1.
    So if B19 = Q4, then MATCH returns 4 (it's in the 4th cell of E1:T1)

    So we have
    INDEX($E$2:$T$13,0,4)

    INDEX returns the range intersecting at the specified Row# and Column# of the specified Range
    INDEX(Range,Row#, Column#)
    So if you did INDEX(A1:G10,3,4) you'd have D3 (3rd row 4th column)

    When a 0 is used for the Row argument, then it returns a range that is ALL the Rows at the column specified
    INDEX($E$2:$T$13,0,4) = the 4th column, All Rows = H2:H13


    The reverse is true if you put a number in the Row argument, and 0 in the Column
    INDEX($E$2:$T$13,4,0) = Row 4, All Columns = E5:T5. (Row 5 is the 4th row of E2:T13, 2 is the first, 3 is the 2nd, 4 is the 3rd)

    Hope that helps.

  7. #7
    Registered User
    Join Date
    01-18-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    Re: SUMIFS help needed - multiple criteria (horizontal and vertical)

    That is great! Thanks for the helpful understanding.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFS help needed - multiple criteria (horizontal and vertical)

    You're welcome.

+ 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(?) I need to sum data using both horizontal and vertical criteria
    By bighandsam in forum Excel General
    Replies: 22
    Last Post: 01-06-2017, 11:31 PM
  2. Sum with multiple criteria Horizontal and Vertical
    By freqzz in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2015, 04:39 AM
  3. Sum Multiple Criteria horizontal and vertical
    By baronk in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2015, 07:55 AM
  4. Searching matching vertical and horizontal criteria in multiple sheets
    By Bloozntooz in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-14-2015, 04:12 PM
  5. [SOLVED] Summing Data with Multiple Criteria on Horizontal and Vertical Axis'
    By tlscowden in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2015, 03:11 PM
  6. [SOLVED] Matching multiple criteria in two books, one data is horizontal, the other is vertical.
    By udrmichelle in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-18-2015, 04:58 PM
  7. Replies: 5
    Last Post: 06-06-2013, 05:12 PM

Tags for this Thread

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