Hi,
This sum =SUMPRODUCT((OB2013!$A$11:$A$106=A11)*(OB2013!$H$11:INDEX(OB2013!$H$11:$S$106,ROWS(OB2013!$H$11:$S$106),$A$1)))
Looks up the figure in column A1 which will be between 1 & 12 for the 12 months.
The returns the sum of the cells between Columns H:S so in month 1 just H in Month 2 H:I, month 3 H:J and so on.
But when I enter 12 it goes to #Value!
Any ideas why anoyone?
I can't post the workbooks there are mutiple links and it is a monster of a workbook.
I ran a simple version changing only the worksheet's name. It worked for all months. Try to look for a non-numeric value in month 12 that's causing the value#! response.
@rcm's observation may be spot on - there may be a non-numeric value in the range to be summed, particularly in the 12th column. that is typically the issue behind #VALUE errors.
if you cannot do anything about the data that you get, then you can use SUM function in an array construct to achieve your desired result.
also, INDEX portion of the formula may be simplified thus (which is one function call less):
so the formula could be:
Please Login or Register to view this content.
confirm this formula with CONTROL + SHIFT + ENTER.Please Login or Register to view this content.
SUM will disregard text values and only use numerical values during addition, which SUMPRODUCT does not do. between the two methods, SUM in an array construct may have a slight edge in terms of performance.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks