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

shakethingsup

Board Regular
Joined
May 21, 2017
Messages
52
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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
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
52
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
14,761
Office Version
  1. 365
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
52
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,116
Messages
5,835,476
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top