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

shakethingsup

Board Regular
Joined
May 21, 2017
Messages
50
Office Version
  1. 2013
Platform
  1. Windows
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.

1595017563763.png


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

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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
May 21, 2017
Messages
50
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
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)))
 
Solution

shakethingsup

Board Regular
Joined
May 21, 2017
Messages
50
Office Version
  1. 2013
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,110
Messages
5,545,993
Members
410,720
Latest member
SSL
Top