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

[mgodwin_tul]

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

[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?

[mgodwin_tul]

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!

[Melvosh]

You're welcome, happy to help!

[mgodwin_tul]

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.

[Melvosh]

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.

[mgodwin_tul]

That worked perfectly! Thanks!

[Melvosh]

You're welcome