Forum

Notifications
Clear all

GROUP 1 * GROUP 2 by Month and Summary of all data from data validation cell.

3 Posts
2 Users
0 Reactions
99 Views
(@ayemu7)
Posts: 197
Reputable Member
Topic starter
 

Hi Team,

Question 1

I have 2 groups. GROUP 1 and GROUP 2. I would like to have my result for GROUP 1- BDCF *GROUP 2- BDCF for relevant month.

Question 2

I also set up a data model referring data validation cell which is from DimFacility as attached. I would like to have summary of expected result of all Accounts and Facilities (validated cell).

Please see attached masked data for reference.

 

Thank you so much for all your support.

Regards,

Aye

 
Posted : 21/03/2021 7:15 am
Anders Sehlstedt
(@sehlsan)
Posts: 971
Prominent Member
 

Hello,

One way to do this is to use INDEX & MATCH for group 1 and a second for group 2.

Example based on your sample file data.
=INDEX(C6:J15,MATCH(A30,A6:A15,0),MATCH(D29,C5:J5,0))*INDEX(C20:J26,MATCH(A30,A20:A26,0),MATCH(D29,C19:J19,0))

If your data structure is like in your sample file I strongly advice you to get your data in an Excel table in a tabular format. Makes it much easier to get needed data dynamically.

Br,
Anders

 
Posted : 21/03/2021 6:57 pm
(@ayemu7)
Posts: 197
Reputable Member
Topic starter
 

Many thanks for your reply, Anders.

ofcourse, Index Match works fine. I am really appreciated.

I will try to use Tubular format as per your adivice.

Regards,

Aye

 
Posted : 22/03/2021 5:27 am
Share: