+ Reply to Thread
Results 1 to 13 of 13

sumif, function picking wrong value

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2016
    Posts
    76

    sumif, function picking wrong value

    Hi There,

    I'm doing sumif and its not working correctly and need some help! The sumif is only picking first value. I want the cell where it says Date to be dynamic
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: sumif, function picking wrong value

    You would normally use SUMIF like this:

    =SUMIF(criteria_range,criteria,sum_range)

    but for your criteria_range you only have one cell (i.e. A1), thus you will only get one value from your sum_range (i.e. from A2). You would normally have dates in one column and then have the corresponding values in an adjacent column.

    Please explain more clearly what you want to achieve.

    Hope this helps.

    Pete

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: sumif, function picking wrong value

    The RANGE and the SUM Range need to be the same size. You are only getting one result, because the RANGE is only one cell in size.

    Also the syntax is not correct - what are you trying to achieve?
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  4. #4
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2016
    Posts
    76

    Re: sumif, function picking wrong value

    Thanks for the assistance!

    All I want to do is result in D2 using sum if and the reason to do that is I've data going back 12 months and need the caculcation based on cell E1. Data is in similar format as posted just extra columns.

    I hope it clear up a wee bit!

  5. #5
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2016
    Posts
    76

    Re: sumif, function picking wrong value

    @Speshul
    I figured out that part coz, if i add the similar range it works, but its not a clean way to write!

  6. #6
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: sumif, function picking wrong value

    may be this is what you wanted
    Attached Files Attached Files

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: sumif, function picking wrong value

    Attach another sample file which shows more dates - do you have different monthly values in different columns?

    Pete

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: sumif, function picking wrong value

    Observation: You are comparing A1 and E1. A1 looks like a date but is only text that looks like a date while E1 is a real date. Change A1 to be a real date. Then the following will work.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  9. #9
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2016
    Posts
    76

    Re: sumif, function picking wrong value

    Please see attached with more datasets. I need sum total based on the month in E1..
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2016
    Posts
    76

    Re: sumif, function picking wrong value

    @AlanY I think you reply gives me the answer but honestly I don't understand the logic!

  11. #11
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: sumif, function picking wrong value

    Quote Originally Posted by nicci113 View Post
    @AlanY I think you reply gives me the answer but honestly I don't understand the logic!
    It's just match the date on E1 to A1 to D1 then add the column the matched all the way down,
    Both the range A1 to D1 and A2 to A50 can be amend to suit

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: sumif, function picking wrong value

    Using your supplied data, this works

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: sumif, function picking wrong value

    Based on your attachment in Post #9, you can use this formula in E2:

    =SUM(INDEX(A2:C14,,MATCH(E1,A1:C1,0)))

    which is non-volatile, but you have to put the date 01/07/2014 in cell A1 instead of the text value "Jul-2014" (copy the format from B1 to A1 using the Format Painter).

    Hope this helps.

    Pete

+ 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] dependant comboboxes picking up wrong items
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2013, 01:46 PM
  2. Sum if array picking up wrong values
    By Loisw in forum Excel General
    Replies: 3
    Last Post: 06-28-2010, 10:21 AM
  3. vlookup function, picking up the wrong row
    By superdan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-03-2009, 10:11 AM
  4. Sumif function gives wrong result on Large worksheet ?
    By kienminwong in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-25-2005, 05:18 AM
  5. labels in formulas picking up wrong range
    By Carmen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-19-2005, 02:24 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