Need help returning the number of times 2 events occur simultaneously

Hey everyone,

So im trying to create a 2D Matrix to see how many times 2 unique events occur. For example, I need to create a table which illustrates the number of movies which have a genre of action AND adventure, action AND comedy, etc.

I have a sheet in excel which has the movies and corresponding associated genres. any help would be greatly appreciated!

Post a sample workbook. Should be fairly straightforward using SUMPRODUCT.

Pete

You can try this:

Formula:

Please Login or Register  to view this content.

Where the movie names are in column A2:A7, the first genre is in column B2:B7, and the second genre is in column C2:C7

- moo

The problem is that I have 7 columns of genres and the associate genre 'action' could be in any column (not just column B)

Post the workbook not the picture

please see the attached workbook. Really appreciate the help/guidance!

thoughts anyone?

bump. really need help with this folks!

I tried the following:

=SUMPRODUCT(--(Table2[[Primary Genre]:[Column7]]="420"),--(Table2[[Primary Genre]:[Column7]]="Adventure"))

but the result is 0, even though it should be 1...anybody have an ideas of why it is returning the incorrect value?

Thanks!

bumping this in a last attempt to get some help!

If I knew enough VBA to get this going, I would love to help. And I can't think of how to create a formula that would return the values needed without it being an enormous mess... sorry.

- Moo

would it be easier if I organize the table so that, for example, the first 4 columns are genres(action, adventure, etc), while columns 4-8 are categories (movies your dad likes, true epics, etc.)?

I am thinking this is really a job for VBA. You might try posting this topic over in the Programming/VBA section and see what pops up. Be sure to include your sample file.

- Moo