Need help modifying a complex SUMPRODUCT search algorithm

This request may be a bit of a doozy...

I have the following data:

	A	B	C	D	E	F	G	H	I
1	Raid Week	Date	Hrs Raided	Guild Max	Attendees
2	1	5/13/2013	15	25	Ariston	Veev	Muffin	Value
3	1	5/19/2013	10	25	Veev	Value
4	2	5/20/2013	4	4	Veev	Puddin
5	4	6/3/2013	2	2	Veev	Ariston	Ariston
6	5	6/12/2013	30	35	Veev	Value	Puddin	Veev	Veev
7	5	6/13/2013	5	35	Value

I currently have the following formula:

Formula:

Please Login or Register  to view this content.

Which attempts to determine if the name "Veev" is present on any given row, and add up the "Hrs Raided" column for each row that it is present. It works fine if there is only one occurrence of the name, but it will give credit for multiple occurrences, which I don't want it to do. I tried to fix that by changing it to:

Formula:

Please Login or Register  to view this content.

Which correctly removed duplicate credit, but then it started giving credit for rows where the name wasn't present. To get this formula working as intended, it should be outputting 61, but it is currently outputting 66.

Then, I want to complicate things a little further... as you can see in the column A, there are 4 unique raid weeks. I want each raid week to yield a maximum value of 20, and I would like for that user's credit to be their attendance for that raid week (while considering the 20 max). E.g., raid week 1 has 25 hours. Veev was present for 25 out of (20) hours and receives max credit of 100%. Ariston was present for 15 out of (20) hours and receives 75% credit.

I really have no idea how to go about doing this. I was thinking to generate a list of unique raid weeks, and to combine that in a SUMPRODUCT along with the names... e.g., min( SUMPRODUCT(1,"Veev",*Hrs Raided) , 20) / min(20,Guild Max) That's not the proper syntax obviously, but hopefully explains my logic behind it. Only, I would like for this to support an infinite amount of raid weeks, and I'm not sure how I would just combine them in the above scenario.

Since this is all a bit confusing, let me give a few examples of how I would like to data to finally be outputted:
Veev = 20/20 + 4/4 + 2/2 + 20/20 = 4.00
Ariston = 15/20 + 0/4 + 2/2 + 0/20 = 1.75
Muffin = 15/20 + 0/4 + 0/2 + 0/20 = 0.75
Value = 20/20 + 0/4 + 0/2 + 20/20 = 2.00
Puddin = 0/20 + 4/4 + 0/2 + 20/20 = 2.00

I hope that makes sense... essentially, I want to award every name with their % attendance for the raid week, while only tracking up to a maximum of 20 hours in a given week, and only giving them up to 100% attendance. Is this doable without macros/VB? I'm attaching the actual spreadsheet since it will undoubtedly be easier to play around with there. Would really appreciate any help.

Voodoo-WildStar-DKP.xlsm

voodoo DKP.xlsx

see if you like this layout

TRy this,
For total hours raided.

Please Login or Register  to view this content.

For %ge

Please Login or Register  to view this content.

Originally Posted by allives

Attachment 246680

see if you like this layout

If this is the only feasible way to accomplish what I'm asking, then I could consider trying it. But I really want to keep the current layout/format that I have, for ease of use with importing in the future. I'm going to be importing lengthy attendance lists on a daily basis, and it's much easier for me to see a row of names that were present to check for any inaccuracies.

Originally Posted by kvsrinivasamurthy

TRy this,
For total hours raided.

Please Login or Register  to view this content.

For %ge

Please Login or Register  to view this content.

The first part works 100%, and thank you very much for your reply. However, is there anyway to do this without having to create a block of code for every individual column? I may have up to 100 different attendees eventually, meaning it would call for some pretty ugly code checking for every name... but I'd be okay with it I suppose, since it would all be copy-pastable with a cell reference in place of the name.

The second part works, but it only works for the first week. E.g., for Veev, it returns 100%, and for Ariston it returns 75%. Eventually, I plan on having a large number of raid weeks, so I need the code to be future-proof without having to manually edit in each new raid week. I realize this is asking a lot. My ultimate goal right now is to find a way to get "20/20 + 4/4 + 2/2 + 20/20 = 4.00" for Veev, while making sure it supports an unlimited number of raid weeks (and preferably attendees). Is there anyway to use the code you gave to get 4.00 for Veev, and 1.75 for Ariston? It seems very close to what I'm ultimately after, but it's sort of hard for me to decipher.

Originally Posted by Bighamm

Veev,

This would be another alternative way to display your data. If you could explain in depth the various columns you have and the data that are provided in them that would help a lot. The only thing I really had a hard time understanding was the "Guild Max". From what I could decipher it seemed as if it was the possible hours that could have been raided for the week. Let me know if that is inaccurate. I'm attaching the values that I came up with based on your data. If this is something you would like to pursue let me know.

Attachment 246762

Thanks for your response, Bighamm, but as I mentioned above, I'm really trying to avoid changing that layout if at all possible. I'm going to be importing names on a massive scale and on a frequent basis. Names of attendees will be coming and going, and it's not really feasible for me to create a dedicated section for tracking attendance of each member. And the "guild max" just represents the number of hours that the guild raided for that particular raid week. I just set it up more as a helper column or for troubleshooting for now. And as I mentioned before, I only want attendance to track up to 20 hours a week. So if Veev was present for 22 hrs out of a 25 hr guild raid week, then Veev would still receive 100% credit. If present for 18 hrs out of a 20 hr guild raid week, then only 90% credit.

Originally Posted by FDibbins

Try this option for the 1st part....

Make J a helper column and use this, copied down...
=COUNTIF(E2:I2,"veev")

Then use this for the sum...
=SUMIFS(C2:C7,J2:J7,">=1")

Thanks, FDibbins. That appears to work, but it goes back to where I'm trying to set this spreadsheet up for importing on a massive scale and frequent basis again, and I'm not sure if it would be feasible to try and have a helper column for every individual attendee, when I may have up to 100 eventually, and those 100 will be constantly changing. The first block of code that kvsrinivasamurthy linked worked very well, with the only downside that it has to be setup for 100+ columns.


Thanks for the replies everyone, really appreciate it. Hope I'm not coming across as too picky or anything, but I just have to set this up to be completely future proof and to require very little maintenance.

Originally Posted by BBL

If this is the only feasible way to accomplish what I'm asking, then I could consider trying it. But I really want to keep the current layout/format that I have, for ease of use with importing in the future. I'm going to be importing lengthy attendance lists on a daily basis, and it's much easier for me to see a row of names that were present to check for any inaccuracies.

can you post a list of the raid attendees i want to see how the raw data is, might be easier to do the calculation off the list and just post the total on excel

Originally Posted by allives

can you post a list of the raid attendees i want to see how the raw data is, might be easier to do the calculation off the list and just post the total on excel

If you go to the first tab of the spreadsheet ("Roster"), those are 25 sample names that could appear. Eventually, that list will be about twice as long, but I don't have all of the potential names at the moment.

On any given day, I need to import an attendance list that could contain any number of those participants. The credit column on the first tab is where I am trying to calculate each person's individual attendance credit.

People will always be coming and going from the roster, which is why it's not too feasible to try and create a dedicated section to every attendee on the "Attendance" tab. Hope that makes sense...

Voodoo-WildStar-DKP.xlsm
your spreadsheet was a bit too complex for me to do a simplified version of what you wanted done. i added in 2 more tabs to do some calculations. I added in some comments in the new tabs to help you keep it updated. let me know you what you think or if you have any questions

Thanks a TON for all of your hard work, allives... looks really good. I've been somewhat busy lately. I quickly checked it out and it definitely seems to accomplish what I was after... going to play around with it a bit later to see if I can populate the names from cell references, so I only have to change the roster on the main tab. Will post again tomorrow when I have more time to sit down and tinker with it. Thanks!