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  Register To Reply

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?  Register To Reply

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!  Register To Reply

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

You're welcome, happy to help!  Register To Reply

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.  Register To Reply

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.  Register To Reply

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

That worked perfectly! Thanks!  Register To Reply

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

You're welcome   Register To Reply