# Any smarter Way to get the result ?

#### Nair

##### New Member
I am trying to get the productivity of machines
data availability
1) Data entry sheet with different products process with machine used and different capacity - start date and end date
2) a separate sheet with each machines capacity per day - for different capacity products

Currently to get what result i wanted - the grey highlighted result (4) by manually first making a unique list of all products
then get total qty using count , vlookup to get the capacity per day for product for the machine

Please refer the attached file. advise any smarter way ! specially avoiding to create any manual list summary etc

#### Attachments

• 12.2 KB Views: 5

#### p45cal

##### Well-Known Member
3 questions:
1. In cell E16 you have formula =COUNTIFS(D3:D12,D16)
In the cell below that you have =COUNTIFS(D4:D13,D17) and so on below. I doubt this is right and I think the formula in cell E16 should be:
=COUNTIFS(\$D\$3:\$D\$12,D16) and that should be copied down, so that the \$D\$3:\$D\$12 should be the same for all the cells below.
Unfortunately, doing this results in 8.5 in cell F22 which gives you a productivity of 121% in cell I21!
Is this right?

2. The Productivity value in cell I21 is dependent on the 7 in I19; where does this 7 come from?

3. What version of Excel are you using?

#### p45cal

##### Well-Known Member
We can do something (with Power Query) like below where the green table incorporates the vlookup and splits multiple day lines into individual day lines.
After that it's a case of knowing where the actuals come from and incorporating that into the likes of a pivot table. The green table won't be visible to the user.

#### Nair

##### New Member
Apologies. corrected file as attached. using Excel 2019

3 questions:
1. In cell E16 you have formula =COUNTIFS(D3:D12,D16)
In the cell below that you have =COUNTIFS(D4:D13,D17) and so on below. I doubt this is right and I think the formula in cell E16 should be:
=COUNTIFS(\$D\$3:\$D\$12,D16) and that should be copied down, so that the \$D\$3:\$D\$12 should be the same for all the cells below.
Unfortunately, doing this results in 8.5 in cell F22 which gives you a productivity of 121% in cell I21!
Is this right?

2. The Productivity value in cell I21 is dependent on the 7 in I19; where does this 7 come from?

3. What version of Excel are you using?

#### Attachments

• 12.4 KB Views: 2

#### Nair

##### New Member
We can do something (with Power Query) like below where the green table incorporates the vlookup and splits multiple day lines into individual day lines.
After that it's a case of knowing where the actuals come from and incorporating that into the likes of a pivot table. The green table won't be visible to the user.
View attachment 78879
The actual scenario is the main data will have multiple different machines for different product combination ( Oil, capacity )
as per the file (revised )
One way i tried is - a work sheet , where i run array commands and got unique values from main data ( which taking lot of memory and slowing down my file other process)
then done the vlookup count etc to reach the productivity . and used that data back in report.

#### bosco_yip

##### Excel Ninja
Try,

1] Calculate the "Actual days" will use NETWORKDAYS.INTL () and Weekend number is 16 for exclude Friday only

2] I make adjusted the tables data in changing the last 2 data into machine type : "LV2", so that the summary formula result can be worked with criteria "LV2"

Then,

3] In "Result" O6, array formula ("Ctrl"+"Shift"+Enter") copied down :

'=IF(N6="","",IFERROR(SUM(SUMIFS(L:L,I:I,IF(B\$16:B\$23=N6,B\$16:B\$23,0),J:J,IF(B\$16:B\$23=N6,C\$16:C\$23),K:K,IF(B\$16:B\$23=N6,D\$16:D\$23,0))*IF(B\$16:B\$23=N6,E\$16:E\$23,0))/NETWORKDAYS.INTL(AGGREGATE(15,6,E\$3:E\$11/(G\$3:G\$11=N6),1),AGGREGATE(14,6,F\$3:F\$11/(G\$3:G\$11=N6),1),16),""))

#### Attachments

• 15.6 KB Views: 4

#### Nair

##### New Member
Try,

1] Calculate the "Actual days" will use NETWORKDAYS.INTL () and Weekend number is 16 for exclude Friday only

2] I make adjusted the tables data in changing the last 2 data into machine type : "LV2", so that the summary formula result can be worked with criteria "LV2"

Then,

3] In "Result" O6, array formula ("Ctrl"+"Shift"+Enter") copied down :

'=IF(N6="","",IFERROR(SUM(SUMIFS(L:L,I:I,IF(B\$16:B\$23=N6,B\$16:B\$23,0),J:J,IF(B\$16:B\$23=N6,C\$16:C\$23),K:K,IF(B\$16:B\$23=N6,D\$16:D\$23,0))*IF(B\$16:B\$23=N6,E\$16:E\$23,0))/NETWORKDAYS.INTL(AGGREGATE(15,6,E\$3:E\$11/(G\$3:G\$11=N6),1),AGGREGATE(14,6,F\$3:F\$11/(G\$3:G\$11=N6),1),16),""))

View attachment 78887
Thanks Mate. this will suffice the requirement using the manual created summary ( B15:F23)
as per my orignal worksheet which is using the manual created working range summary.

any way we can avoid this manual created summary ? direct using data from ( B2: G12 ) and using range (I2:L11)
avoiding the manual created summary B15:f23 is the actual requirement as i had to create a seperte working sheet
with array formula to derive the unique data from range ( B2:G11) and that slow down the process

#### p45cal

##### Well-Known Member
The actual scenario is the main data will have multiple different machines for different product combination ( Oil, capacity )
as per the file (revised )
That's fine, my offering will take account of that as well as allowing different start and end dates on the same line.
You've confirmed you have Power Query built in so that's fine too.

But I'm still after where your actuals come from; there is no sign of that in your attachment except for a single cell value, and presumably that's for one machine only (LV1).

If I can get actuals from you (hopefully in a table with dates) I can get you your reports in a flexible way.