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

1. ## 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.

Cheers,
Matt

Capture.PNG

2. ## 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?

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

Originally Posted by Melvosh
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!

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

You're welcome, happy to help!

5. ## 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. ## 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. ## Re: Return sum of values from column if date found in row array

That worked perfectly! Thanks!

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

You're welcome

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

#### 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