Look up multiple instances of a value and return

[ryan.smoke]

Hi there, I'm having trouble rearranging data pulled from an API into a useful format. I am running Excel 2010 on Windows 7.

Basically what I have are individual events (with event category names) occurring multiple times on different dates. When the data is updated and pulled into Excel, it is sorted by date and then by the category name. If different event categories occur on the same day, multiple instances of the same date will occur in column B (I've colored the rows corresponding to the same date for clarity). If no events occur on a given date, nothing will show up. Likewise, if events of only one category occur, the other event categories will not show up.

What I want to do is create a separate table, shown on the right, that is automatically populated with events of each category for each corresponding date including dates on which no events occur (I want the table to include zeros for these dates).

So I guess what I'm asking is how do I wright a formula corresponding to each day in the table on the right that looks for multiple instances of the same date in the table on the left and correctly populates visits in the table on the right in their appropriate columns according to event category name.

Any help anyone might be able to give would be very much appreciated!

Thank You!
Ryan

Picture: http://i54.tinypic.com/11hvszo.png

-- removed inline image ---

 

[Haseeb Avarakkan]

Hi, Welcome to MrExcel.

Try this in H4, copy across & down...

=SUMIFS(D:D,B:B,$G4,C:C,H$3)

 

[ryan.smoke]

Thank you Haseeb this works like a charm! I really appreciate your help!

Ryan