Question related to Pivot Tables

Hi,

I am making a list of pharmaceutical products and the indication (medical conditions) which they are meant to treat. Sometime one product has more than one indication (one medicine treats more than one disease).

My rows are the product name, and then I have 5 columns for up to 5 indications ("indication 1",...,"indication 5") and extra columns to keep track pf the company that makes the product and what country it is made in.

I want to see in a pivot table how many products I have that treat a specific indication. For example, how many products treat hepatitis B. It is fairly easy to make a pivot table and put the indication on one axis and just have a count. The problem is, if a product treats more than one indication (for example, hepatitis A and B), a pivot table will not count both. For example, if column "indicator 1" says hepatitis A and column "indicator 2" says hepatitis B, if I summarize the column "indicator 1" this product will not be counted as treating hepatitis B (since it is in a different column), and I will have one less count of products which treat hepatitis B.

Is there a way to combine the different columns ("Indicator 1":"Indicator 5") and see the total count? The one solution I could think of is to make a line for each drug-indication pair such that only one indication is allowed per line (so in the above example I would have two lines for the same medicine, one with indication hepatitis A and one with hepatitis B). However, another piece of information I have about each medicine is the company that makes it and the country where it is made. This second approach will completely throw off any pivot tables that try to look at summary of drugs-per-company or drugs-per-country (since I will have the same drug appearing many times, once for each indication).

Any ideas?

Thanks!

Yoav

If you could provide a sample of what you have so far I can try a couple of things.

Matuna,

Thank you for the offer. I am attaching a sample file (my real file is huge).

You can see that there are 5 different products for treatment of TB: products K to O.

In the pivot worksheet, however, we only count 2, since it is looking at "indicator 1". I need it to somehow count all three indicator columns, and get a count of 5.

Cheers,

Yoav