SumProduct by year Issue

**JO505** · 03-03-2013, 04:06 PM

Attached is a sample of a file I am having an issue with, namely the Sum by Year section on the Jobs by Type sheet. Fir some reason I can't get the formula to work when changing years. The sum by Customer section works fine but everything stops when trying to sum by year. I have looked a formats and I think they are fine (they do work when sunning by customer). Any help would be appreciated.

Jim O

**Norie** · 03-03-2013, 04:14 PM

There are empty strings in the cells below the dates.

**JO505** · 03-03-2013, 04:40 PM

I see. Is their a way to work around that as the range will always be changing and will most certainly have blank cells.

Jim O

**Dunc3142** · 03-03-2013, 04:37 PM

See if this will work for you. I have calculated for just the year in column B, and changed the font color to the same as the background.

**Norie** · 03-03-2013, 04:44 PM

Jim

Change the formulas on the Job worksheet to return 0 instead of "".

You can hide the 0s with formatting or conditional formatting.

**JO505** · 03-03-2013, 05:15 PM

Norie,

I am not sure I am following you correctly. The cost column on the Jobs sheet has no formulas the data is entered directly. I went through the data and changed the blank cells to 0 and I can get the formula to work only if I adjust the range in the formula itself. In the summary sheet cell B2 allows the data to be changed and with it the range to select from. I need a dynamic formula that will take into account the changing range.

Jim O

**Norie** · 03-03-2013, 05:27 PM

It's not the cost column that's the problem, it's the date column.

On 'Jobs by Type you have this formula in E5, copied down to E100.

Formula:



  =Job!Q2

In Q2 down on 'Job' you have this formula.

Formula:



=IF($O2="","",INDEX($A$1:$E$201,$O2,3))

If O2 on 'Job' is "" then this formula will return "", so the formulas in column E on 'Job by Type' will return "" if column O on 'Job' is empty.

If you change the formula on 'Job' to this the SUMPRODUCT will calculate correctly.

Formula:



=IF($O2="",0,INDEX($A$1:$E$201,$O2,3))

Or you could create dyanmic named ranges.

**Dunc3142** · 03-03-2013, 05:30 PM

Doesn't my earilier post do what you want? The attachemnt has changes in it.

**JO505** · 03-03-2013, 06:28 PM

Norie,

Thank you for the input.

Dunc3142,

Thank you as well I missed your first post.

Both solutions are very good I am not sure which I will go with but both have been very helpful.

Again Thanks to both of you,

Jim O