+ Reply to Thread
Results 1 to 7 of 7

SUMIFS returns VALUE!

  1. #1
    Registered User
    Join Date
    06-04-2010
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    9

    SUMIFS returns VALUE!

    Hi,

    I'm using the SUMIFS function and am finding that using the following code always returns a VALUE! error, even when I use the Math and Trig wizard.

    Please Login or Register  to view this content.
    Please can you help?

    Thanks

    Mike

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: SUMIFS returns VALUE!

    With SUMIFS all ranges need to be the same size (and shape). B3:C5 is double the size of the other two ranges so that won't work. Can you explain what you want to do? If B3:B5 might contain "North" then surely sum range should be C3:C5?, i.e.

    =SUMIFS(C3:C5,B3:B5,"north",C3:C5,1000)
    Audere est facere

  3. #3
    Registered User
    Join Date
    06-04-2010
    Location
    Dhaka
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: SUMIFS returns VALUE!

    Quote Originally Posted by mogdog1981 View Post
    Hi,

    I'm using the SUMIFS function and am finding that using the following code always returns a VALUE! error, even when I use the Math and Trig wizard.

    Please Login or Register  to view this content.
    Please can you help?

    Thanks

    Mike
    Ref : Sumifs()

    What i have understood is your formula code contains some error. your formula might be =SUMIFS(C3:C5,B3:B5,"north",C3:C5,1000). Because you are adding only the values contained in C3:C5 not in B3:C5.

    Jasim

  4. #4
    Registered User
    Join Date
    06-04-2010
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: SUMIFS returns VALUE!

    Thanks guys, that has solved the problem. I'm quite new to this (as you may have guessed!), want i don't understand is why SUMIFS only works if the "north" arguement looks at both ranges, (C3:C5, B3:B5) while the "1000" arguement looks at just the one range (C3:C5), why is that?

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: SUMIFS returns VALUE!

    mogdog,

    want i don't understand is why SUMIFS only works if the "north" arguement looks at both ranges, (C3:C5, B3:B5)
    It does not look at both ranges for the "north" argument. The syntax for Sumifs is

    =Sumifs(sum_range, criteria_range1, criteria1, criteria_rangen, criterian)

    So, the first argument is not where it looks for any comparison but for the values to be summed if the ensuing criteria are met in their respective ranges.

  6. #6
    Registered User
    Join Date
    02-03-2011
    Location
    Centurion South Africa
    MS-Off Ver
    Excel 2007
    Posts
    1

    Exclamation Re: SUMIFS returns VALUE!

    I have a different problem ... or maybe not so different.
    I want to check horizontally and vertically and then sum.

    Example of how I want it to work with the formula being in E2:E4
    A B C D E
    1 true true false Total
    2 true 1 1 1 2
    3 true 2 2 2 4
    4 false 3 3 3 0


    =SUMIFS(B2:D2,B1:D1,TRUE,A2:A4,TRUE)

    This however returns the dreaded #VALUE! error

    Any ideas will be appreciated!!

  7. #7
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: SUMIFS returns VALUE!

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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