countifs: Related item(s)

[rrocker1405]

Hi,

I'm working on related item(s) countifs to populate information. However, I dont know if there is a easier way to show up information for the same thereby I planned to use index. Attached is the sheet with my criterian stated in i11 cell.

I would be more than happy if there is an easier way to pick information and show up data. I have got more than 20000 entries and this one is a sample for te same.

Thanks in advace.

Kind regards,
Anand

 

[prasaddn]

Hi,

can you help me understand this piece of your requirement..
"Should there be more than one related item, then I should have one per related item in the count ifs or sumproduct."

Also, what about those records where there is no cluster nor related ID?

Regards,
Prasad DN

 

[prasaddn]

I was going thru your file and got one more question in mind, what if the related ID is also not have any cluster.... like related ID = 4.

Regards,
Prasad DN

 

[rrocker1405]

Hi,

what I'm tyring to do is basically the below:
1. count for all items with cluster data
2. some items like SUV, Basic and Process will not have cluster information but they have related id's based on which I should be able to pick up information.
3. There can be situations in which a item like SUV might be related to more than one related item. for example suv is related to ids 9, 10, 11, 12, etc.., the cluster information of 9, 10, 11, 12 should be could be counted for SUV.
4. for the item's which are blank like 4 should be counted as 0.

Please let me know if i anwered your qery.

Kind regards,
A!

 

[Nebu]

Hi:

Please find the attached, not sure is this what you are looking for, I have used helper columns to get the counts...

 

[jeffreyweir]

Crossposted at http://forum.chandoo.org/threads/formulas-using-vba.20736/

Anand: Can you please post just one question in future? Posting the same question in two separate places just adds extra work.

Have you considered whether a PivotTable can assist you with this problem?