+ Reply to Thread
Results 1 to 8 of 8

Return sum of values from column if date found in row array

  1. #1
    Registered User
    Join Date
    05-20-2019
    Location
    New Orleans, LA
    MS-Off Ver
    Excel 2016 for PC
    Posts
    6

    Return sum of values from column if date found in row array

    Hi all,

    I'm attempting to write a formula to tell me the total number of subjects I'll be working with on a given day. For example, if 28-May (W2) appears in the array F31:M48, I need W4 to return the sum of the number of subjects (B31:B48) from all of the studies where it appears (4 total). I know that number should be 36 based on manually looking at the array and adding the numbers, but I would like to have this number returned automatically.

    Currently, I'm using the "CountIf" function to tell me how many studies I'm working on that day but would like to know the total number of subjects.

    Any advice?

    Cheers,
    Matt

    Capture.PNG
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Return sum of values from column if date found in row array

    Try this array formula, entered using ctrl+shift+enter:
    Please Login or Register  to view this content.
    I tested this in W4 and it returned 36. Does that give you the results you're looking for?
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  3. #3
    Registered User
    Join Date
    05-20-2019
    Location
    New Orleans, LA
    MS-Off Ver
    Excel 2016 for PC
    Posts
    6

    Re: Return sum of values from column if date found in row array

    Quote Originally Posted by Melvosh View Post
    Try this array formula, entered using ctrl+shift+enter:
    Please Login or Register  to view this content.
    I tested this in W4 and it returned 36. Does that give you the results you're looking for?
    Hi Melvosh,

    Unfortunately, when I put in the formula as you've expressed it, I get a #VALUE error. I'm not sure why. Initially I copy/pasted the formula but when I got the error, I tried typing it in directly to make sure there wasn't some issue with the copy/paste.

    Any idea why that might be happening?

    Matt

    EDIT: I figured it out. I didn't enter it as an array formula! It works brilliantly! Thanks!
    Attached Files Attached Files
    Last edited by mgodwin_tul; 05-20-2019 at 06:33 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Return sum of values from column if date found in row array

    You're welcome, happy to help!

  5. #5
    Registered User
    Join Date
    05-20-2019
    Location
    New Orleans, LA
    MS-Off Ver
    Excel 2016 for PC
    Posts
    6

    Re: Return sum of values from column if date found in row array

    Is there a way to return a hyphen if the value is 0? I know I can use the "If" function but I'm not sure how that works within the context of an array formula. I tried adding it to the "false" portion of the current "if" function but it didn't change anything.

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Return sum of values from column if date found in row array

    The simplest way would be to use a custom number format that replaces a 0 with a hyphen. Otherwise you'll have to test the entire result of the array formula, which can add a fair amount of overhead calculations.

  7. #7
    Registered User
    Join Date
    05-20-2019
    Location
    New Orleans, LA
    MS-Off Ver
    Excel 2016 for PC
    Posts
    6

    Re: Return sum of values from column if date found in row array

    That worked perfectly! Thanks!

  8. #8
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Return sum of values from column if date found in row array

    You're welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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