+ Reply to Thread
Results 1 to 7 of 7

Summing a column on another sheet depending on a date

  1. #1
    Registered User
    Join Date
    07-24-2017
    Location
    Laddonia, Missouri
    MS-Off Ver
    2013
    Posts
    3

    Summing a column on another sheet depending on a date

    I have a worksheet that I that I want a total of column in another worksheet =SUMIF('LAD 1099 Smartlist'!A:A,'W9-1099'!A9,'LAD 1099 Smartlist'!J:J) which I was able to get the only thing I am having trouble with is I want it to only take the sum of the column if it is in that month of the column name so I want it to depend on another column in that worksheet (Smartlist) column H which the date is formatted like 1/25/2017. Column H is in the Smartlist worksheet. So I believe I need to use SUMIFS but everything I have tried is not working.
    Attached Files Attached Files
    Last edited by gadams31; 07-24-2017 at 03:49 PM. Reason: upload sheet

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Summing a column on another sheet depending on a date

    Hi gadams31, welcome to Excel Forum! A small sample workbook (NOT a picture) would really help us to solve your problem quickly and accurately.
    Remove any sensitive or extraneous info, just show us what cells contain your data and formulae, what results you want, and where you want them.

    To attach a workbook:
    Click Edit Post (or just start a new reply.)
    Click Go Advanced
    Scroll down to Manage Attachments and click.
    Now just Browse for your file, then click Upload. Simple!

  3. #3
    Registered User
    Join Date
    07-24-2017
    Location
    Laddonia, Missouri
    MS-Off Ver
    2013
    Posts
    3

    Re: Summing a column on another sheet depending on a date

    ok it's there

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Summing a column on another sheet depending on a date

    Updated 17:47 - fixed relative addresses.
    Try this. I had to change your monthly column headings to numeric dates (1/1/2017,2/1/2017, etc.) so they could be used for comparison. Then I formatted those cells to show "Jan", "Feb", etc.
    Paste in K3, copy across then down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Attached Files Attached Files
    Last edited by leelnich; 07-24-2017 at 05:48 PM.

  5. #5
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Summing a column on another sheet depending on a date

    Post #4 issues fixed. (Some Relative addresses changed to Absolute.)

  6. #6
    Registered User
    Join Date
    07-24-2017
    Location
    Laddonia, Missouri
    MS-Off Ver
    2013
    Posts
    3

    Re: Summing a column on another sheet depending on a date

    Thank you could you explain in English what this part of the formula did. I see the greater than or equal to k2 what does the & symbol do? and I am assuming it is saying less than EOMONTH end of month correct?

  7. #7
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Summing a column on another sheet depending on a date

    Syntax- SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

    The formula adds each amount whose associated values meet the following conditions:
    1) Vendor ID is correct AND 2) Date is >= 1st day of the month [K2] AND 3) Date is <= last day of the month [EOMONTH(K2,0)].

    Each SUMIFS() criteria is actually a text "string" consisting of a COMPARISON OPERATOR (< = >) and a COMPARISON VALUE. Since they are text, any LITERALS, including comparison operators (s), must be enclosed in "". Literals grouped within "" are then combined with VARIABLES (calculated results or cell references) using the CONCATENATION OPERATOR &, which functions like a plus sign for letters and text.
    Last edited by leelnich; 07-25-2017 at 06:16 PM.

+ 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. Macro to copy & paste data into new sheet depending on column header (date)?
    By BobbyH89 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2015, 03:44 PM
  2. Replies: 1
    Last Post: 04-10-2014, 04:29 AM
  3. Replies: 5
    Last Post: 08-23-2013, 07:29 AM
  4. [SOLVED] Macro to update the date column for certain rows in a table depending on a column value
    By ajolin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-18-2012, 12:21 AM
  5. [SOLVED] Summing columns depending on date selected
    By john_london in forum Excel General
    Replies: 2
    Last Post: 09-10-2012, 09:56 AM
  6. Replies: 2
    Last Post: 08-22-2012, 04:24 PM
  7. [SOLVED] Summing one column based on date in another column
    By excel guru i'm not in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 12-30-2005, 04:45 PM

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