+ Reply to Thread
Results 1 to 6 of 6

SUMIFS problem

  1. 07-03-2020, 12:06 AM #1
    sinweaver
    sinweaver is offline
    Registered User
    Join Date
    03-12-2019
    Location
    Earth
    MS-Off Ver
    2016
    Posts
    8

    SUMIFS problem

    Hi folks,

    I spent a long while looking but still not exactly sure what I did wrong.
    My understanding is that SUMIFS sums up the value in selected cells that fulfills multiple conditions.

    Here's my problem, I have a list of values listed under each week (for more legible representation of the table, please refer to imgur.com/a/GBFo4Zj)
    1-Jul-20 8-Jul-20 15-Jul-20 22-Jul-20 29-Jul-20 5-Aug-20 12-Aug-20 19-Aug-20 26-Aug-20 2-Sep-20
    -$275.00 -$275.00 -$275.00 -$275.00 -$275.00 -$275.00 -$275.00 -$275.00 -$275.00 -$275.00
    -$30.13 $0.00 $0.00 $0.00 -$30.13 $0.00 $0.00 $0.00 -$30.13 $0.00
    $0.00 $0.00 $0.00 -$19.99 $0.00 $0.00 $0.00 -$19.99 $0.00 $0.00
    $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
    $0.00 -$110.26 $0.00 $0.00 $0.00 -$110.26 $0.00 $0.00 $0.00 $0.00

    Assuming that the first column for 1-Jul-20 is in cell C4, the cells I wanted to calculate is cell C5 to C19, and that, in a separate sheet, I have the date criteria in cell B5.
    The formula I entered was =SUMIFS('Sheet7'!C5:C19, 'Sheet7'!$4:$4, B$5)
    It returns #VALUE, which really doesn't really help much.

    Any guidance will be appreciated. Thanks.
    Reply With QuoteRegister To Reply
  2. 07-03-2020, 01:13 AM #2
    Gregb11
    Gregb11 is offline
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,823

    Re: SUMIFS problem

    It's always best to actually attach a sample file (please read the yellow banner at the top of this page), but failing that, maybe try this formula:

    =SUMPRODUCT(('Sheet7'!C5:M19)*('Sheet7'!C4:M4=B$5))
    Reply With QuoteRegister To Reply
  3. 07-03-2020, 01:19 AM #3
    AliGW
    AliGW is offline
    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
    80,410

    Re: SUMIFS problem

    Your title is not great, but I will let it go this time.

    The ranges in the formula need to be exactly the sane length for it to work and need to be on the same axis - you are trying to compare a row with a column, so it will never work.

    Administrative Note:

    Welcome to the forum.

    Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

    Thank you for helping us to help you.
    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.
    Reply With QuoteRegister To Reply
  4. 07-03-2020, 07:18 PM #4
    sinweaver
    sinweaver is offline
    Registered User
    Join Date
    03-12-2019
    Location
    Earth
    MS-Off Ver
    2016
    Posts
    8

    Re: SUMIFS problem

    Quote Originally Posted by Gregb11 View Post
    It's always best to actually attach a sample file (please read the yellow banner at the top of this page), but failing that, maybe try this formula:

    =SUMPRODUCT(('Sheet7'!C5:M19)*('Sheet7'!C4:M4=B$5))
    Thanks, Greg, here's a sample sheet.
    In the meantime, I will give that a go.

    I am aware that if the cell with the value I want to sum up is fixed, I can always use SUM(), but I am curious why SUMIFS did not work in this instance.


    AliGW,

    Not entirely sure how to phrase the title as I couldn't understand what went wrong.
    Appreciate your explanation, but I am not quite sure what you mean since what I am intending to do is sum the column if a cell matches another cell.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 07-03-2020, 11:51 PM #5
    AliGW
    AliGW is offline
    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
    80,410

    Re: SUMIFS problem

    Please update your location entry in your profile.

    =SUMIFS('Sheet7'!C5:C19, 'Sheet7'!$4:$4, B$5)

    In the formula you have a vertical sum range of 14 cells in length. The criteria range is the whole of row 4, which is horizontal. SUMIFS requires ranges that are parallel to each other and of the same length, so your formula is obviously wrong. Does this make sense?

    Not entirely sure how to phrase the title as I couldn't understand what went wrong.
    Your title is meant to tell us what you are trying to do, not how it should be done or why what you have tried is failing - your helpers will deal with that.
    Last edited by AliGW; 07-04-2020 at 12:54 AM.
    Reply With QuoteRegister To Reply
  6. 07-04-2020, 02:29 AM #6
    AliGW
    AliGW is offline
    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
    80,410

    Re: SUMIFS problem

    In B5 copied across:

    =SUMPRODUCT((Sheet7!$C$3:$AP$3='Summary Tracking'!$B$1)*Sheet7!$C$21:$AP$21)

    In B6 copied across:

    =SUMPRODUCT((Sheet7!$C$3:$AP$3='Summary Tracking'!$B$1)*Sheet7!$C$5:$AP$19)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 Problem
    By 51m0n in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-09-2018, 03:40 PM
  2. [SOLVED] Sumifs problem
    By Villalobos in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-17-2015, 08:12 AM
  3. problem with sumifs
    By ankitsharma1 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-31-2014, 07:47 AM
  4. SUMIFS problem
    By Villalobos in forum Excel General
    Replies: 8
    Last Post: 08-12-2014, 05:12 PM
  5. Problem with Sumifs
    By Jhawk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2013, 02:27 PM
  6. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  7. SUMIFS Problem
    By denise001 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2011, 08:38 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1