UMID | Box Number | Format |

1 | 1 | 16mm |

2 | 1 | 16mm |

3 | 1 | 8mm |

4 | 2 | 16mm |

5 | 2 | 35mm |

6 | 2 | 3/4" Umatic |

7 | 3 | VHS |

8 | 1 | VHS |

9 | 1 | 1/4" tape |

<tbody>

</tbody>

I want to write a formula that will take this information from "Sheet 1" and populate cells in a "Sheet 2". I will need to populate cells with information such as "Number of items" per box and "Formats" in each box. I'm wanting formulas that will essentially help me get results like this:

Box Number | Number of items | Media Types | UMIDs |

1 | 5 | 16mm, 8mm, VHS, 1/4"Tape | 1,2,3,8,9 |

2 | 3 | 16mm, 35mm, 3/4"Umatic | 4,5,6 |

3 | 1 | VHS | 7 |

<tbody>

</tbody>

I know that I can accomplish this with =COUNTIF(Sheet1!BoxNumber,1) for instance for counting the number of items within a box. Is there a better formula I could be using?

What I'm most concerned about is, How can I populate the cells in my "Mediatypes" and "UMIDs" columns like in the examples I've listed?

Thanks for any help!