+ Reply to Thread
Results 1 to 8 of 8

Help with SumIf

  1. #1
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Help with SumIf

    In the attached workbook Range H100 to H104 I have a SumIf, =SUMIF(G3:G96,G100:G104,D3:D96), this works great on ALL the data.

    Firstly, could this be made dynamic as the formula only works upto row 96 and my dataset will grow.

    Secondly, how can I get it to only SumIf between dates, e.g. From 24 October 2021 To 05 January 2022 but by using the cell E99 & E101 references on the sheet.

    Do I need VBA for this?
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with SumIf

    1. not sure why you posted this in the VBA forum?
    2. You could Structured Tables for the data input - the range will increase as the table grows (you may need to move the summary table though)
    3. Try using sumifS() to include dates.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Help with SumIf

    Try the below sum in H100 to include the dates in the calculation.
    =SUMIFS(D3:D96,G3:G96,G100:G104,A3:A96,">="&E99,A3:A96,"<="&E101)

    As Ford rightly points out, move the summary table to another sheet or to the right of the data table and it will make your life easier when it comes to making it dynamic.
    For example, move it so that the "From" cell you currently have in E98 is in L2. You could then reference entire columns like this:
    =SUMIFS(D:D,G:G,N4:N8,A:A,">="&L3,A:A,"<="&L5)

    Hope that helps.

    BSB
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: Help with SumIf

    Thanks for replying so quickly.

    My mistake putting this in the VBA forum.

    The attached workbook is just an example, the "real" data is in a table.

    I have spent hours with the sumif and sumifs trying to get the date part to work, I couldn't get it to work even if I hard coded the date into it, so I am stuck right now.

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Help with SumIf

    Quote Originally Posted by kjg View Post
    The attached workbook is just an example, the "real" data is in a table.
    If the data is in a table then you can reference that rather than the entire column approach I mention.

    =SUMIFS(Table1[Purchased Weight Kg''s],Table1[Form No],N4:N8,Table1[Date of Landing],">="&L3,Table1[Date of Landing],"<="&L5)

    Obviously change instances of "Table1" in the formula to your actual table name.

    BSB

  6. #6
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: Help with SumIf

    Thank You BSB,

    I have put your first example into my sample data and checked the results and it worked perfectly.

    Since then I noticed your other reply regarding the table, I will try that later, but I am sure it will work just as well.

    Thanks very much.

  7. #7
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Help with SumIf

    No problem. Happy to help
    Have a good day!

    BSB

  8. #8
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: Help with SumIf

    Just tried the latest formula from BSB on real data in table, and as expected, works perfect.



    More than happy to get help

    My day is now starting...........its raining

    Stay Safe
    Last edited by kjg; 07-03-2021 at 02:08 AM. Reason: More information

+ 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] Sumif - Trying to figure out how to tell a sumif to look for multiple naming conventions
    By Typirious in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2017, 02:11 AM
  2. [SOLVED] SUMIF: don't understand that purpose of the OFFSET inside SUMIF
    By Vitalite in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-30-2017, 03:13 AM
  3. Replies: 2
    Last Post: 06-21-2017, 06:32 PM
  4. SumIf formula or Sumif with pivottable combined question
    By mcgleeuw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2015, 01:05 PM
  5. Replies: 4
    Last Post: 12-04-2014, 02:06 PM
  6. [SOLVED] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  7. Nested SUMIF statement or multiple SUMIF's
    By Dan27 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2009, 06:55 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