+ Reply to Thread
Results 1 to 6 of 6

SUMIFS problem

  1. #1
    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.

  2. #2
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    708

    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))
    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,486

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    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

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,486

    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.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,486

    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 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 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1