sumproduct, indirect, substitute, address - different sheets in a workbook - #Value! error - cumulative sum

[shakethingsup]

1. is there an issue with this formula when using it on different worksheets? and why?
2. open to alternative formulas for the task at hand

The objective is to be able to sum cumulatively based on a user selection of the month of the year.

I have a worksheet with 2 tabs. One is called 'Summary' and the other is called 'Data'. The Data tab has columns for 12 months, multiple rows with similar mapping codes. See below for a small sample.

In the 'Summary' tab, the user will select a month, and it will show the cumulative summarized results by each mapping code.

Example:
1. user selects April. Medication will sum to ($36,635.43).
2. user selects June. Medication will sum to $43,774.54 which is the sum of all values.

When I put this formula in the 'Data' tab where all my raw data is it worked:

=SUMPRODUCT((Data!B5:B7=Summary!E5)*Data!$C$5:INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(Summary!$A$1,Data!$C$1:$N$1,0)+2,4),"1","7")))

But I want this formula in the 'Summary' tab in Cell F5 and instead I get a #Value! error. It's the exact same formula.

Thank you!

 

[jasonb75]

Same formula but different data? The most likely cause will be text in the range $C$5:INDIRECT(...)

Note blank cell containing a formula is text.

Any other issues with the formula would give you #N/A or #REF errors instead.

 

[shakethingsup]

Same formula but different data? The most likely cause will be text in the range $C$5:INDIRECT(...)

Note blank cell containing a formula is text.

Any other issues with the formula would give you #N/A or #REF errors instead.

Hey thanks for responding. I didn't quite follow.

It's pulling the same data, correctly referenced to the right worksheets, ranges, etc.

I'll try and look at this with fresh eyes on Monday.


would you go about this with a different formula? or am I on the right track?

 

[jasonb75]

Not sure which part you didn't follow, the only thing I can see that would cause #VALUE! would be text in the range of C5:N7. This would include any formulas that show "" when there is no data to display but not empty cells.

Regarding different formula, there are many ways to do it, personally, I would drop the indirect address method in favour of index.

=SUMPRODUCT((Data!$B$5:$B$7=Summary!E5)*Data!$C$5:INDEX(Data!$C$5:$N$7,0,MATCH(Summary!$A$1,Data!$C$1:$N$1,0)))

 

[shakethingsup]

Not sure which part you didn't follow, the only thing I can see that would cause #VALUE! would be text in the range of C5:N7. This would include any formulas that show "" when there is no data to display but not empty cells.

Regarding different formula, there are many ways to do it, personally, I would drop the indirect address method in favour of index.

=SUMPRODUCT((Data!$B$5:$B$7=Summary!E5)*Data!$C$5:INDEX(Data!$C$5:$N$7,0,MATCH(Summary!$A$1,Data!$C$1:$N$1,0)))

Thank you! - this worked like a charm. Sorry meant to post earlier!