Need help to populate table from tally table

[Viral Patel]

Hi

I am trying create a table that populate the analysis table from the Tally table but It sounds like a complicated formula.
I have attached the excel sheet with how I would like the analysis sheet being completed.

Please could you help me.
Viral

[#2]

Viral,

You don't have to use any complex formula if you got an alternative and easier solution!

You can use the PivotTable which is powerful, easy, and time saver data analysis tool!

I've updated your workbook with the solution!

[#3]

Hi
Thanks for the reply however I don't want to use a pivot table only formula.

[#4]

Ok, I've updated the workbook with a formula solution!

Anyway, it's not as complex as you think!

Please find the attached file.

[#5]

Hi

Thanks for you answer.
However you have summed up the number of occurrences that id1 blue has. In the table you uploaded u have got 3 however it should b jus 1.

Please could you have a look at the original excel file I uploaded. The analysis table should look like the same but using formula.

Kind regards

[#6]

It's done with this formula:

=IF(SUMIFS(B$3:B$11,$A$3:$A$11,$I3),1,"")

Hope that helps!