• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Sumproduct

GaryMc

New Member
=(SUMPRODUCT(--(LEFT(Results[CIT13_Hits],1)="5"),--(INDEX(Results,0,MATCH($H$4,Results[#Headers],0))="CY")))

How do I make the table header names eg [Cit13_Hits] and the associated value “5” dynamic do I don’t have to edit the formula when these names & values change.

Thank you.

Gary
 
Hi Gary, please follow the site rules and upload a sample workbook representative for your case and create a manual solution, so we clearly understand what you are after. Like it is presented know, I guess no-one will be able to answer correctly. We would be guessing, assuming... That is not ideal.
 
Sorry, how is this:

Instead of manually changing the Hits Code [BT3_Hits, CIT13_Hits, FC4Hits etc] & the value i'm looking for in the Left function "1", "2", "3" etc.
I wanted to use their respective cell references so that I can simply copy the formula down and right and not have to manually changes these variables in the formula. However it does not work so i'm looking for advise / alternative methods. Thanks.

59321
 
Prepare a desensitised sample workbook that we can work on. No need to share the real, confidential file.
 
I think moving the field of interest one column right each time you move down a row in the summary table is inherently a problem.
Instead, I have returned results for the entire results table (all fields) but transposed the result to give a column. Rather than the 'Hits Code' being used within a MATCH formula, it is then simply the transpose of the table headers. The MMULT is used to perform a row by row calculations on a 2D array (other aggregations process the entire array to give a single result).
 

Attachments

  • Transposed lookup.xlsx
    16.9 KB · Views: 15
OK, thanks Ali, see attached, I hope it provides you with what is required.
The section for attention is in yellow, section 1, however the same issue applies in section 3 & 6
 

Attachments

  • Chandoo_GaryMc1.xlsx
    50.7 KB · Views: 6
I think moving the field of interest one column right each time you move down a row in the summary table is inherently a problem.
Instead, I have returned results for the entire results table (all fields) but transposed the result to give a column. Rather than the 'Hits Code' being used within a MATCH formula, it is then simply the transpose of the table headers. The MMULT is used to perform a row by row calculations on a 2D array (other aggregations process the entire array to give a single result).

Thanks Peter, i'll take a look at that, much appreciated.
 
Try,

In O8, formula copied across right to W8 and all copied down :

=SUMPRODUCT((LEFT(INDIRECT("Results["&$B8&"]"),1)=O$7&"")*(INDEX(Results,0,MATCH($H$6,Results[#Headers],0))="CY"))

Regards
Bosco
 

Attachments

  • Chandoo_GaryMc2.xlsx
    50.8 KB · Views: 11
Try,

In O8, formula copied across right to W8 and all copied down :

=SUMPRODUCT((LEFT(INDIRECT("Results["&$B8&"]"),1)=O$7&"")*(INDEX(Results,0,MATCH($H$6,Results[#Headers],0))="CY"))

Regards
Bosco
Very Much Appreciated Bosco, Thank you. - Good old Indirect :0)
 
I think moving the field of interest one column right each time you move down a row in the summary table is inherently a problem.
Instead, I have returned results for the entire results table (all fields) but transposed the result to give a column. Rather than the 'Hits Code' being used within a MATCH formula, it is then simply the transpose of the table headers. The MMULT is used to perform a row by row calculations on a 2D array (other aggregations process the entire array to give a single result).

Thanks Peter, i'll take a look at that, much appreciated.

Thank you again Peter, now that is way of looking at it I would never have been able to come up with and I will use your post to develop my understanding further. Thank you.

One thing I cant work out though, the named field 'Digit' seems to be floating depending upon where the cursor is. I may be missing something but I cant work it out / understand out to create it.

I'm also having a little trouble getting a value other 0 after hitting shift ctrl enter, this may be due to my lack of understanding the named field 'digit' of course. I amended my file Chandoo_GaryMc1.xls and applied your formula structure but can't seem to get it to work. My O8 cell formula adapted from your file:

= MMULT( TRANSPOSE( --(LEFT(Results[[FC1hits]:[FC25hits]],1)=TEXT(digit,"0")) ), --(Results[CO]="CY") ) -

Thanks.
 
Gary
Your observation that the named field 'Digit' appears to be floating is correct. A name is not necessarily an absolute range; it is a formula that is revaluated every time the name is referenced. In this case the formula is
= B$2
The row '2' is absolute but the column 'B' is relative to the active cell.

I actually wrote the formula using a pre-release dynamic-array version of Excel in which there is a special notation for this, namely
= @digits
where '@' is the implicit intersection operator.

The reason I needed to resort to such devices is that your result data has 3 dimensions. These are the records of your results table (which has to be aggregated using an array function such as SUMPRODUCT), the 'hits' code (which is transposed in the output table) and the 'digits'. Since spreadsheets are based upon 2D arrays, something has to give and I treated each digit as a separate problem.
 
This is the solution within your sample spreadsheet. It looks consistent but I have yet to verify that it gives your results.
 

Attachments

  • Chandoo_GaryMc1 (PB).xlsx
    51.4 KB · Views: 11
Back
Top