+ Reply to Thread
Results 1 to 6 of 6

Testing for date and calling cell data from that date for a formula

  1. #1
    Registered User
    Join Date
    10-22-2018
    Location
    Pacific NW USA
    MS-Off Ver
    Professional Plus 2016
    Posts
    7

    Testing for date and calling cell data from that date for a formula

    Hi, all,
    This is my first post on the forum, after doing an intro in that area. I'm growing my knowledge of Excel through various projects and hope you might help me with a specific challenge. Please forgive me if I make some mistake in the form of my query.

    I have a table containing data relating to various health and fitness statistics and activities (table is in its infancy and unpoplated so no real point in linking it here). I am hoping to develop a formula that allows the following:

    In a particular cell of a table, lets say Z1, I want to input a formula that examines column A and
    • identifies all cells in that column that contain a particular date (either a specified date or the current date--either would work);
    • for all rows that have that date in column A, look to other specified columns (let's say D and E); and
    • uses the data in D and E as inputs for a formula, the result of which is finally displayed in Z1. I don't yet know the formula, but let's assume it's a simple product of the two numbers.

    For context, the result in cell Z1 with be a calculation of work performed on a specified date, using elements from cells (in our example) in column D and E. The elements may appear in just one row, or multiple rows. In other words, there may be more than one row in Column A that have the same date. In sum, I want cell Z1 to calculate the PRODUCT of cell D and E all rows with the same date in cell A and then to SUM all of those PRODUCTS.

    I hope my question is clear and that I have asked it in an appropriate manner. Thanks!

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Testing for date and calling cell data from that date for a formula

    Welcome to the Forum hammock!

    If you need the sum of products, the SUMPRODUCT function is what you're looking for. Excel Help pages or a Google search will provide much documentation on how it works:

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


    If your desired date is in a cell, rather than TODAY(), just update the formula accordingly.

    (table is in its infancy and unpoplated so no real point in linking it here)
    Since you did not provide a sample file with sample data, I could not test my formula. In the future consider providing a file, even if the data is fake.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-22-2018
    Location
    Pacific NW USA
    MS-Off Ver
    Professional Plus 2016
    Posts
    7

    Re: Testing for date and calling cell data from that date for a formula

    Jeff, thanks for the help. I'll dummy up a file for future questions. And I'll take a look at this again tomorrow, then mark the thread closed unless I post a follow up.

    Cheers.

  4. #4
    Registered User
    Join Date
    10-22-2018
    Location
    Pacific NW USA
    MS-Off Ver
    Professional Plus 2016
    Posts
    7

    Re: Testing for date and calling cell data from that date for a formula

    Okay, I'm learning how to use the forum correctly, thanks for the guidance. I have attached a file for reference and testing.

    My goal is to come up with a formula for cell H8 that is easily copied and pasted into other cells of that column in future. I want it to examine Column A, identify the date in that column and,
    using that date as a reference, give me the sum of products of column D*E*F for that date. In the attached file, that would mean the sum of (110*5*11)+(90*3*8), or 8210.
    Attached Files Attached Files

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Testing for date and calling cell data from that date for a formula

    OK, now you have added column F to the multiplication. You just need to extend the original formula I provided. Also, you first said "either a specified date or the current date--either would work" and now you want the date in each row. So here is the revised formula, which is implemented in the attached file:

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

  6. #6
    Registered User
    Join Date
    10-22-2018
    Location
    Pacific NW USA
    MS-Off Ver
    Professional Plus 2016
    Posts
    7

    Re: Testing for date and calling cell data from that date for a formula

    Thank you very much, Jeff. Since I was attaching the file I decided to implement those changes you mentioned, so as to end up with the most helpful info. Once again, thanks!

+ 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] Formula for converting a date to quarter and leaving blank cell for no date values
    By jayc2111 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-10-2019, 12:57 AM
  2. Replies: 4
    Last Post: 03-21-2018, 09:25 AM
  3. testing if one date exceeds today's date by a specific number of days with vba
    By Dana_Carter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2016, 07:49 PM
  4. Looking to display data in a cell, only if today's date is 7 days past a cell's date
    By mbenderpetassure in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2015, 01:57 PM
  5. Replies: 1
    Last Post: 10-02-2012, 02:42 PM
  6. Testing a Date
    By LNA in forum Excel General
    Replies: 1
    Last Post: 08-29-2006, 02:27 AM
  7. [SOLVED] testing a cell for a date value
    By Karen53 in forum Excel General
    Replies: 3
    Last Post: 07-15-2006, 12: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