Count of Multiple sub categories

Hi
I have attached the sample excel of exact replica of original with sheet names input and output. Obviously output is what required. It is little complicated. I would really appreciate your timely help on this.
Excel version - 2010

grouped by names first then date, plan, risk....as shown in the output
Name1. date1, date2...plan1,
Name2. date1, date2...plan1

Formula:

Please Login or Register  to view this content.

in b3 of outout sheet and make sure the type1 should be same in both in Input & Output sheet

Punnam

thanks for the reply. Let me make it some more clear
If Name1 in output matches with input, then it should bring out the values of corresponding dates,plan,risk . After which under types it should give count on respective match

Did the function worked the requirement ?

Sorry punnam. It was taking only counts of types. However if Name1 in output matches with input, then it should bring out the values of corresponding dates ,plan, risk. Col.B,C,D of Output sheet is variable based on Input sheet(If Name1 matches it should bring these fields from Input).

@ krajivgandhi
i Have uploaded the revised sheet in post 9 Check is this what you are looking for ?
Punnam

exactly. and then those count formulas will work accordingly. Thanks

wow...we are almost there. But I have checked it for name2, name3 with array formula. it did not work. may be I have gone wrong somewhere. pls refer attached and correct me

hi,

necessary changes have done please check the sheet.

Punnam

You will need to use an approach shown in the attached:

in INPUT M:O

Table which counts number of entries for each name plus cumulative (O2 must be 0)

in B3 of OUPUT

=IFERROR(INDEX(INPUT!$A$2:$A$12,SMALL(IF(INPUT!$B$2:$B$12=OUTPUT!$A3,ROW(INPUT!$B$2:$B$12)-ROW(INPUT!$B$2)+1),ROWS($1:1)-VLOOKUP($A3,INPUT!$M$2:$O$4,3,0))),0)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.


Copy down

repeat for c and D changing highlighted range

Ideally we need additional formula to create "Names" column A in output

I am "out of the office" for most of the day.

Hi Punnam, Sorry for the late reply. In the attached excel, you have put formula for Name 3. However the excel had not picked corresponding data for Name 3. Could you please help on this regard

Hi john, Thanks a lot. Your attachment is suffice.