Help! Extracting Unique Records & creating a dynamic table

[SekharS]

Hello Chandoo Community,

I am new to the forums & looking for help.

Background:
I have a spreadsheet attached in which
1. We are trying to identify 'Implementation Channel' for all our Customer & non-customer project effort.
This is a manual work in progress, some may never be identified (meaning, blank cells).
2. This is a mirror sheet for base data elsewhere.
3. For 2018 (meaning addtl. rows in future) , we plan to update base data every Quarter, for new trends etc.

Now to the problem I want to solve:
1. I want to create using Excel formulas (not VBA, which I can't manage).
2. A dynamic table (or 2 tables), for now, of 'Customer Project Effort' (Col B).
2. An average for each 'Implementation Channel', per year.
3. A count for 'Implementation Channel', in that year.

As we progressively identify 'Implementation Channels' for Customer & non-Customer Project Effort, I would like to use/customize these dynamic tables for 'non-Customer Project Effort' as well.

Hope I was able to explain myself clearly.

I have looked at:

https://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/
& few other posts , but I am not getting through...

Any help is greatly appreciated.

Thanks
Sekhar

 

[p45cal]

In the attached, a dropdown in cell N1 to choose amongst Customer, non-Customer, or both.
The left row headers are the Implementation channels, and the rest of the columns are Count and Average of each year.

Sorry, I shouldn't have left a little macro in there, it doesn't need to run, it was just a quick way of formatting the results.
…oh, and it's a pivot table.

 

[Nightlytic]

P45cal beat me to it

Formula based tables if you're interested

 

[p45cal]

Just doing a cross-check mine v. Nightlytic's results, makes me realise that, subject to confirmation from SekharS, any 0 (zero) doesn't want to be part of the count or the average. That being the case, Nightlytic's count is correct, but his averages include zeroes. Mine includes zeroes for both the count and the averages both of which are probably wrong, so I attach another offering which is hopefully correct for both averages and counts (I just deleted all zero values in the source data so that they're blanks).

 

[SekharS]

Hello p45cal & Nightlytic.

Thanks a ton!!! for quick help.
Both are really helpful, but Nightlytic's formula based solution will be more appropriate to my need.

p45cal - As you pointed out, the count in Nyghtlytic's sheet doesn't include zeros, but the average takes them into account. This doesn't help.

Nyghtlytic - can help & incorporate this requirement into formula?
Also, Couple of questions:
1. there are 33 unique values in Col A of Sheet1, you worked on. Only 29 made it to Col A of Sheet2.
2. A dumber qstn, still: When I customize this formula from Sheet2!A4, and try entering it as an array formula in my workbook, it gives me a blank. If I add {}, it stays there as text. Any pointers?

Thanks again life savers

Best Regs
SekharS

 

[bosco_yip]

Another formula base option,

1] K5, copied down :
=IF(ROWS($1:1)<=X$2,MAX(K$4:K4)+1,"")

2] L5, copied down :

=IF(K5="","",(INDEX(A$2:A$3000,MATCH(1,MMULT(ISNA(MATCH(A$2:A$3000,L$4:L4,0))*(B$2:B$3000=L$1),1),0))))

3] M5, copied across to S5 and all copied down :

=IF($K5="","",CHOOSE(IF($L$2="COUNT",1,2),COUNTIFS($A$2:$A$3000,$L5,$B$2:$B$3000,$L$1,C$2:C$3000,">0"),AVERAGEIFS(C$2:C$3000,$A$2:$A$3000,$L5,$B$2:$B$3000,$L$1)))

Regards
Bosco

 

[SekharS]

Thanks bosco_yip.

This works well.
The only edit I need is the formula in Col M-thru-S NOT considering zero(0) while calculating Averages.

Can you help?