Duplicates counting with plural criteria

[Pulci]

This is a follow-up of my previous post which was brilliantly solved. I found out that I need someting additional, which is over my head really.

I know have the following formula: IF(SUMPRODUCT(($G$4:$G$3000=$G6)*($AA$4:$AA$3000=AA6)*($O$4:$O$3000=O6)*($I$4:$I$3000=I6))>1,....

However, I want to change the end into something which can count the number of appearances of the duplications in a specific row, so I would get results saying that they appear 1 time, 2 times, 3 times or 4 times (4 being the most) in the list and could be placed after the text value of O and I (I398&" "&O398,"-"))?

I hope I formulated this well enough....

Also, does anybody have an idea how I can message my file? I've got close to 3000 rows all the way up to AB and my pc now starts to complain...

Many, many thanks, you guys are wizards!

[teylyn]

It's a bit hard to imagine your data and data layout. Can you post a small data sample and illustrate your need?

[Pulci]

yes of course, please see attached

cheers!

[teylyn]

The attached sample does not produce any results for your duplicate formula. Can you include some data that does and then please mock up what result you would like to see and explain the logic.

[Pulci]

The attached sample does not produce any results for your duplicate formula. Can you include some data that does and then please mock up what result you would like to see and explain the logic.

Please find another one attached with values that came from the formula. I would like to see how many times the same title (with the same year, same number, same rating) has been mentioned in the whole list (4 to 3000). This allows me to count the number of times a specific title has been written in collaboration with other authors and helps me not to count that title double in the total number of publications later on. I would like to see this result behind the existing values (e.g. 4A,2B etc).

Its a bit tricky to explain, but I hope you understand.

[teylyn]

So you just want the result of the count that SUMPRODUCT returns? then use

=SUMPRODUCT(($G$2:$G$3000=$G2)*($AA$2:$AA$3000=AA2)*($O$2:$O$3000=O2)*($I$2:$I$3000=I2))

Note that I changed the starting row for the arrays to be row 2, since your data seems to start in that row.

hth

[Pulci]

=SUMPRODUCT(($G$4:$G$3000=$G4)*($AA$4:$AA$3000=AA4)*($O$4:$O$3000=O4)*($I$4:$I$3000=I4)) (data begins at 4, the other was a dummy)

works great! But would it be possible to add the outcome of the value I4 and O4 in front of the count (I4&" "&O4,"-")? So a possible outcome would look like 34A (or something along those lines)? This would help me to locate what is counted.

thanks!

[teylyn]

simply concatenate the cells with the sumproduct

=I4&" "&O4&" "&SUMPRODUCT(($G$4:$G$3000=$G4)*($AA$4:$AA$3000=AA4)*($O$4:$O$3000=O4)*($I$4:$I$3000=I4))

or however you want to arrange them

[Pulci]

this is what I have been looking for teylyn! Many thanks, nice blog by the way (the one in your signature).

http://www.excelforum.com/ is the best excel forum I have seen so far, you guys are legends!

keep it up, I will close this post as solved

thanks again!