Nestled Sum, If and Year

[UnitedSkysJoey]

Hello Mr. Excel community!

I am at a lose.

I have this formula in one workbook and it works flawlessly. I am trying to add it to another workbook with the appropriate changes. But I am not getting the desired result. I am hoping that someone who hasn't stared at the workbook for endless hours can spot my mistake.

I have one sheet that has a date in a cell (I16) that is being referenced in the formula.

I have another sheet that has many rows of data. The header rows are dates.

I want this formula to return the value of the cells that have headers with the same year.

=SUM(IF(YEAR('Skylights P&L'!1:1)=YEAR(I16),'Skylights P&L'!6:6,0))/1000

Instead of returning just those with the matching year it is returning the sum of the entire row.

Thanks in advance for the assistance.

 

[DushiPunda]

Seems like you need to enter the formula as an array formula. HOLD CTRL + SHIFT and hit ENTER.

 

[63falcondude]

Try

=SUM(IF(YEAR('Skylights P&L'!1:1)=YEAR(I16),'Skylights P&L'!6:6))/1000 Ctrl Shift Enter

or

=SUMPRODUCT((YEAR('Skylights P&L'!1:1)=YEAR(I16))*('Skylights P&L'!6:6))/1000

Note that you should change the whole row range to a smaller range such as A1:Z1, A6:Z6, etc.

 

[UnitedSkysJoey]

Thank you for those suggestions. When I turn the formula into an array it displays #VALUE .

I have tried to change the range to eliminate other cells (which kinda defeats the purposes of the formula) and still it isn't working properly.

 

[DushiPunda]

Here's what I did for testing purposes:

Row/Col	A	B	C	D	E	F	G	H	I
1	10/24/17	10/25/18	10/26/17	10/27/17	10/28/18	10/29/17	10/30/17	10/31/18
6	1	2	1	1	2	1	1	2
16									10/1/18

<tbody>
</tbody>

I did this all from the same worksheet. The array formula I used was:

{=SUM(IF(YEAR(1:1)=YEAR(I16),6:6))/1000}

The result I get is 0.006, which is the expected result (2+2+2 = 6/1000 = .006)

So, if you're getting a #VALUE error, I would suggest the following - if you want to keep the entire row in your formula, for testing purposes, change it to the range you have currently (for example, I would change it to A1:H1 and A6:H6). Then, from the formula bar, highlight everything within the "logical test" portion of your IF and then hit F9 (see below for example of what exactly to highlight):

=SUM(IF(YEAR(A1:H1)=YEAR(I16),A6:H6))/1000

Note: You have to change it from the whole row because the result will be too long to display. Anyway, once you hit F9, you'll see something like the following (actual result from my test data provided above):

=SUM(IF({FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE},A6:H6))/1000

So that is what your logical test is returning. For each FALSE, the year does not match the year in I16; for each TRUE, the year does match the year in I16. It goes in order as well...so the first result is the result of the logical test run on the date in A1 (FALSE), then B1 (TRUE) for the second result, then C1 (FALSE), so on and so forth. If you aren't getting what you expect to be getting from that test, then you have something wrong.

 

[UnitedSkysJoey]

Thank you for this.

When i reduce the range from entire row to just a few cells it works. As soon as I change the range it no longer works. This is making me think that the year function can't operate along so many cells?

Either way the solution, at least for now, is to change the range.

 

[63falcondude]

I put the sample data from post #5 into a new worksheet.

Both of the formulas suggested in post #3 gave me 0.006

=SUMPRODUCT((YEAR(1:1)=YEAR(I16))*(6:6))/1000
and
=SUM(IF(YEAR(1:1)=YEAR(I16),6:6))/1000 Ctrl Shift Enter

Since the formulas are on the same sheet as the data (in this sample), I removed the sheet references.

Again, I would recommend against using whole column references in array formulas. This was just to show that they do indeed work.

 

[UnitedSkysJoey]

I am grateful for all the help! Turns out that I had everything correct from the beginning. The issue was that the source data had a cell with text in it, that was throwing the #value situation. Using F9 helped me key in on it.