+ Reply to Thread
Results 1 to 12 of 12

SumIf or array formula

  1. #1
    Registered User
    Join Date
    06-11-2010
    Location
    St. George
    MS-Off Ver
    Excel 2007
    Posts
    12

    SumIf or array formula

    So I've searched this forum and all over the place and can't get anything to work...If I had a better understanding of formulas (as I assume some of you do) I think this would be a pretty straight forward and common formula type. Here is the info I have:

    Column A: Date
    Column E: Part Number
    Column H: Cost of Part
    Column K: Month References
    (all other columns are hidden and not needed)

    Range is A2:A5000

    I am trying to creat a formula to sum the totals of a given part number for a given month. In other words, I need to get my total costs for a certain part number for each month. I can't use cells as references becuase this worksheet will be used by multiple people and sorting will change, changing the reference cells.

    Any help or guidance will be much appreciated!!

    Here are a few of the formulas I tried but couldn't quite get to work....

    {=SUM((A2:A5000="APR")*(E2:E5000="3001-01-111")*H2:H5000)}

    [SUM(IF($A$2:$A$5000=$K2, IF (E$2:E$1000="JAN",D$2:D$1000),0))]

    both formulas gave me zeroes when I should have had numerical results.

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

    Re: Need help with a sumif or array formula

    If column A contains the month as text then your first formula should work......but if it's a date you might want MONTH function, i.e.

    =SUMPRODUCT((MONTH(A2:A5000)=4)*(E2:E5000="3001-01-111"),H2:H5000)
    Audere est facere

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,001

    Re: Need help with a sumif or array formula

    Here is a good read on SumProduct

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

    Re: Need help with a sumif or array formula

    Oh, and the most efficient way in Excel 2007 might be to use SUMIFS function which allows you to sum for multiple conditions, again if date column is the month as text that would be like this

    =SUMIFS(H2:H5000,A2:A5000,"APR",E2:E5000,"3001-01-111")

    but with true dates in column A you'd need an upper and lower bound for the date column like this:

    =SUMIFS(H2:H5000,A2:A5000,">="&DATE(2010,4,1),A2:A5000,"<="&DATE(2010,4,30),E2:E5000,"3001-01-111")

  5. #5
    Registered User
    Join Date
    06-11-2010
    Location
    St. George
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: SumIf or array formula

    Okay I think the month issue is probably what was messing things up! I'll have to give that a try and see where it leads. Thanks for the help!

  6. #6
    Registered User
    Join Date
    06-11-2010
    Location
    St. George
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: SumIf or array formula

    I tried the formulas out and am still getting zeros? I used the formula builder that macs have as an option and came up with one that I think will work best, however, I can't seem to get the dates formatted correctly. SO my question is...how can I format my dates in the formula to correspond with what I have listed (currently listed as date, year, ie Jan-10) or would it be easier to just change all those dates to a text formate such as "JANUARY"??

    Here is the formula I've come up with if someone can verify that it should work...I believe the red is my problem area...

    =SUMIFS('[GRAPH DATA.xlsx]CRJ200'!$H$2:$H$5000,'[GRAPH DATA.xlsx]CRJ200'!$A$2:$A$5000,"(MONTH=1)",'[GRAPH DATA.xlsx]CRJ200'!$E$2:

  7. #7
    Registered User
    Join Date
    04-27-2009
    Location
    Bristow, VA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: SumIf or array formula

    MONTH is a function so (MONTH=1) won't work. You need to use the last form (with DATE function) that Daddylonglegs showed with the SUMIFS function, or the MONTH(A2:A5000) form if you're using SUMPRODUCT.

  8. #8
    Registered User
    Join Date
    06-11-2010
    Location
    St. George
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: SumIf or array formula

    So my formula should work if entered as follows?

    =SUMIFS('[GRAPH DATA.xlsx]CRJ200'!$H$2:$H$5000,'[GRAPH DATA.xlsx]CRJ200'!$A$2:$A$5000,">="&DATE(2010,4,1),A2:A5000,"<="&DATE(2010,4,30),'[GRAPH DATA.xlsx]CRJ200'!$E$2:$E$5000,15839-2)

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

    Re: SumIf or array formula

    Quote Originally Posted by zachvu View Post
    So my formula should work if entered as follows?

    =SUMIFS('[GRAPH DATA.xlsx]CRJ200'!$H$2:$H$5000,'[GRAPH DATA.xlsx]CRJ200'!$A$2:$A$5000,">="&DATE(2010,4,1),A2:A5000,"<="&DATE(2010,4,30),'[GRAPH DATA.xlsx]CRJ200'!$E$2:$E$5000,15839-2)
    I think the second instance of A2:A5000 needs to show the full filename and sheetname as per the first instance.

    Note that SUMIFS doesn't work with closed workbooksso GRAPH DATA needs to be open for the formula to work. If it might not be open then you probably need to revert to SUMPRODUCT......

  10. #10
    Registered User
    Join Date
    06-11-2010
    Location
    St. George
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: SumIf or array formula

    I got the formula to work, I just ended up reformatting the date as a text only piece and locked it so nobody else has access to it!

    I have another question though... when a part number has a +/- sign in it, how can I use that as an if condition without excel thinking it's an operation? I tried single quotations and parentheses but received errors with both....

  11. #11
    Registered User
    Join Date
    06-11-2010
    Location
    St. George
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: SumIf or array formula

    also... it won't allow me to use anything with a combination of numbers and letters such as 0871BN8

  12. #12
    Registered User
    Join Date
    06-11-2010
    Location
    St. George
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: SumIf or array formula

    Nevermind guys...I figured out I just need to use double quoatations instead of single!!! Thanks!!!
    Last edited by zachvu; 06-22-2010 at 04:49 PM.

+ 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