• 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.

Expanding SUM ( ) with tables

D

DashboardNovice

Member
In the attached file, in column B, I am counting how many times [Company, Task Order and Position] has occurred as of any given row. No problem here.

In column H, I am trying to do the same thing, except all of this is part of a table. The problem is trying to refer to individual cells within the table. Is this possible?
 

Attachments

  • Expanding SUM Function with Tables.xlsx
    10.8 KB · Views: 7
You can just use Cell reference as normal.

If you must use structured table reference... you can use INDEX and ROW to resize range.
Ex:
Code: 
=COUNTIF(INDEX([Concat],1):INDEX([Concat],ROW([@Concat])-ROW(tblSales[[#Headers],[Concat]])),[@Concat])
 
If you must use structured references … ??? One of the few properly structured features of Excel :DD!
My suggestion would be to enter the spirit of COUNTIFS. Using a helper column containing the Record Number
= ROW(tblSales[@]) - ROW(tblSales[#Headers])
and ditching the concatenation one is left with
= COUNTIFS( [Company],[@Company], [TaskOrder],[@TaskOrder], [Position],[@Position], [RecordNumber],"<="&[@RecordNumber] )
True it is a bit long (it fails the 'rule of thumb' test) but at least it is meaningful.
 

Attachments

  • Expanding SUM Function with Tables (PB).xlsx
    17.8 KB · Views: 3
If you must use structured references … ???...
Click to expand...
Formula, to me, is always going to be ad-hoc solution. Even though table reference is structured, there is no mechanism that enforce strict adherence.
Think PivotTable, Data Model or M query language. ;)
 
Chihiro said:
You can just use Cell reference as normal.

If you must use structured table reference... you can use INDEX and ROW to resize range.
Ex:
Code: 
=COUNTIF(INDEX([Concat],1):INDEX([Concat],ROW([@Concat])-ROW(tblSales[[#Headers],[Concat]])),[@Concat])
Click to expand...

I'm trying to understand your use of the ROW ( ) where I highlighted in red. Why does ROW( ) have two arguments, Headers and Concat?

Also, how were you able to use Concat without a table name before it? You also do that after INDEX. I removed the table name when referencing Concat and I got an error.
 
ROW(tblSales[[#Headers],[Concat]])
It is not ROW that has two parameters. It is the structured reference that specifies the header row of the 'Concat' column. Since all the headers are on the same row ROW(tblSales[#Headers]) will give the same result.

References within a table to one of its columns can omit the table name; the reference is assumed to be to within the table unless otherwise indicated.
 
Chihiro said:
Formula, to me, is always going to be ad-hoc solution. Even though table reference is structured, there is no mechanism that enforce strict adherence.
Think PivotTable, Data Model or M query language. ;)
Click to expand...
I agree that there are more structured solutions available, especially for data analysis; there is a point where I move to them (despite the fact that my grasp of M and the advanced editor is somewhat tenuous). What I have found by trial and error, though, is that the consistent use of Named Formulas, which are evaluated as arrays, can provide an intermediate level of programming between the formal and ad-hoc. It is intelligible without needing to trace precedents around the worksheet and the number of entities handled is orders of magnitude lower than the number of cells that form part of the solution.
 
In that case. You can do something like below.
Named Ranges:
cRow
Code: 
=INDEX(INDIRECT("tblSales[Concat]"),ROW(INDIRECT("tblSales[@Concat]"))-ROW(INDIRECT("tblSales[[#Headers],[Concat]]")))

fRow
Code: 
=INDEX(INDIRECT("tblSales[Concat]"),1)

Formula in Count Occurrence column then becomes.
Code: 
=COUNTIF(fRow:cRow,[@Concat])
 
lol. Yes you are right. I rarely use table for calculation. I didn't realize you can slice it like that.

Good to know.
 
That was just sneaky, I didn't think of including the Header row in the range to be searched.
Adopting the idea I went on to define a name '↑' that refers to
= tblSales[#Headers] : tblSales[@]

I then avoided the need for the concatenation column by using range intersection on each field, to give the formula
= COUNTIFS( ↑ [Company], [@Company], ↑ [Task Order], [@[Task Order]], ↑ [Position], [@Position] )

A wonder whether there is a society for the prevention of cruelty to end users? :p
 

Attachments

  • Expanding SUM Function with Tables (PB2).xlsx
    11 KB · Views: 2
Peter Bartholomew said:
A wonder whether there is a society for the prevention of cruelty to end users? :p
Click to expand...
Now, there's an idea!

Speaking of which, because those structured table references are naturally working in a relative context, I turned my back on them in formulae for a long period because they seemed to lack the possibility of being used in an absolute context.

It was only when I stumbled upon a series of tutorials by Mike Girvin, aka Excel Is Fun, I learned about those double brackets "[[" and colon ":" combo to mix relative and absolute structured references. Indeed a bit sneaky and cruel. I felt like some-one who for the very first time learns about F4 to toggle cell references. Oh, forgive me of swearing, @Peter. :oops:

Why doesn't the formula intellisense include some hints to that regards when using table references? Starting to type them in a relative context, one can complete them easily by selecting the arguments with the some key strokes (arrow down+tab), but when you add the ":" that experience is gone. Kind of a bummer.
 
Guido.
I use Tables to hold input data very often because that allows the data objects to adjust dynamically. If the data structure is one of records running across a number of fields then I would very likely use the structured references in formulas directly. If the structured reference is too long, though, I also apply a defined name e.g. 'k' to refer to:
= DatabaseTable[Record Number]
Whereas the relative reference can be relative or absolute depending upon the way in which the formula is propagated, k is dynamic but absolute. Definitely an improvement over
= DatabaseTable[[Record Number]:[Record Number]]

Another situation is where I have a number of constants as input/assumptions. There I might have a properties table with columns
Name | Value | Units | Description
In that case the main mode of reference is Create from Selection to apply names to the value column.

For 2D matrices, the column headers are an irrelevance so I switch them off and use the Table name itself as the array name within any formula. Multicell array formulae (CSE or dynamic) have to be outside the Table. I did gain an understanding that not everyone uses array formulas to the extent that I do when Mike Girvin commented:

"As for your COUNTIFS, I have never, in almost 3 decades, seen a spreadsheet like yours with ALL the calculations entered as arrays!!!
Now I see why your are so excited by the new Excel Calculation Engine."

Somewhere along the way, I seem to have departed from mainstream Excel development! ;)
 
You must log in or register to reply here.
Back
Top