Count, Max and Sum

[Mikee_likes_excel]

I am new to excel and I am having difficulty creating a formula that will sum a column and only place the sum in the first row of a group of the same date. Please see attached file. Each row has repeated dates with repeated total pieces I have created a count max formula but am stuck on sum of the max for the group of date.

Any help or insight would be appreciated

 

[Peter Bartholomew]

It's just more of the same. I did find it odd to see Tables but no structured references.

 

[Mikee_likes_excel]

Thank you Peter for such a quick response. I was very close in my attempts prior to posting ... your input helped tremendously. Good point .. I changed the formula to structured references. I'm taking this data a trying to run a pivot table then a pivot chart. These 2 columns will not add or sum in the pivot only count ... like the cells contain text. I've tried several changes including changing the value_if_false from "" to 0 ... but still cannot get them to sum on my pivot table. It's odd because I can sum these 2 columns at the bottom of the table but the pivot table will not sum. Any advise would be appreciated.

 

[GraH - Guido]

A pivot can't sum 2 columns, but values of a certain dimension, which is a single column. Or I misunderstand.
Also why do you need the formulas inside the table. Both can be done via pivots.

 

[Mikee_likes_excel]

Okay ... Attached is a more complete file of what I'm working with. I saved as a .xlsm so you can see the user entry form. I would like the chart to be by day not by day and by tag.
The entry person uses the data entry form to populate the table. The data is inserted into the top row of the table. With every row entry we calculate the percent of recovery for that grade slot (column L) against the inbound tag pcs count (column M). I would like to calculate the recovery percent of the grade slot by day. This means I have to sum up the total pcs count for that "grade slot" by day and divide by the sum of individual tags used per day. So for example the total inbound tag pcs count for 10/20/2020 is 6720 pcs - 4 tags / loads of 1680 pcs each. If you sum all entries for that day the total is 73920 which is not the sum I want to use. It would give me a incorrect answer. The question? What is the percentage of grade slot "Core" for 10/20/2020? ... the answer 400 core pcs divided by 6720 tag pcs or 5.9%. That is ultimately the answer we are looking for - for each grade slot as a percent by day. Hopefully I explained this well enough. Thank you

 

[Peter Bartholomew]

I have had a go with the Pivot tables but I rarely analyse data and are not that familiar with them.

 

[Mikee_likes_excel]

Looks like your idea will work. Although I'm not able to use the formula because my version of Excel (2016) does not include some of the functions you used. The formula has xlfn / xlpm prefix:
= _xlfn.LET(
_xlpm.newtag?, COUNTIF( Tag0:[@Tag], [@Tag]) = 1,
_xlpm.maxpcs, _xlfn.MAXIFS([Pcs.], [Tag], [@Tag] ),
IF(_xlpm.newtag?, _xlpm.maxpcs,"") )
So I'll need to find another Excel 2016 workaround. Any ideas would be appreciated.
Thank you

 

[Peter Bartholomew]

To get the formula to work in Excel 2016 substitute the formulas into the last line in place of their local names.
The alternative is to use defined names to hold the formulas.

 

[GraH - Guido]

Don't have much time free these days, last months even, so my contributions are a bit short and maybe to general, but n this case, I would go with a data model and measures.
It would require some remodelling of data, but at a first scan I do not think any of the intermediate calculations and lookups are required when shifting to PowerPivot. Though one can debate you do make similar measures in DAX.