Summing values based on existence of common values in two strings/arrays

[Stever7]

So I'll keep it brief here as I've attached a Workbook which (I think!) demonstrates the problem more clearly than I can in just words; however just for completeness:

Basically (and putting the problem into a fictitious scenario to make it slightly more transparent:

• Imagine I have a group of students who have been working on various projects
• Each project would have a list of students who worked on it (in the form of integers separated by a number/pound/hash, e.g. 1#3#4#7)
• Each project would also have a score associated with it
• I want to be able to provide a list of students that I want to consider (in the same format as before) and obtain the total score for all projects than any of them have worked on
• Notably I don't care if more than one student being considered on a project, or if it's just one, I only want to add that project total once

A few notes about how I'm looking to (ideally) solve the problem:

• I'm trying to avoid VBA; otherwise I need to jump through some admin hoops that I'd rather avoid for now!
• I'd like to avoid helper cells where possible; although normally I don't have an issue with them. This is mostly because the number of projects and the number of cases (combinations of students) are variable and I want this to be "plug-and-play" rather than needing modification if (for example) more cases are required

I've tried to solve the problem, and I felt like I was on the right lines but I can't work out how to get rid of the need for helper cell. The method involved converting the lists into arrays and then using a combination of OR, ISNUMBER and MATCH; however, frustratingly I could only get it to work when hard coding the arrays in (i.e. pointing them to cells containing the arrays didn't work as they'd just look at the first value in said arrays).

Hopefully I've covered everything needed either here or in the attached spreadsheet, but if I've missed anything (or not explained something well enough) please just let me know

[Bo_Ry]

Please try at D3 and drag down

=SUMPRODUCT((MMULT(--ISNUMBER(FIND(--MID(SUBSTITUTE(C3,"#",REPT(" ",9)),{1,9,18},9),$C$9:$C$13)),ROW($C$9:$C$11)^0)>0)*$D$9:$D$13)

[Stever7]

Hi Bo_Ry,

Firstly, thanks for the help Secondly, your solution seems to work in almost all cases but in my initial test of it I've found one that makes it falls down; If one of the cases is set as "4#5#1#2#3" the calculated total is 1026 but if it's replaced with "1#2#3#4#5" then the calculated total is 1051 (which is correct); not really sure why this case is throwing a spanner in the works :/

[Glenn Kennedy]

It's because it was set up for querying a maximum of 3 students in C3:C5. What is the maximum?

With 1#2#3 it will have covered all 1051 cases, however, with 4#5#1... the 25 for student 2 is omitted.

[Bo_Ry]

This one workup to 5 student

=SUMPRODUCT((MMULT(--ISNUMBER(FIND(--MID(SUBSTITUTE("#"&C3,"#",REPT(" ",9)),COLUMN(INDIRECT("A:E"))*9,9),$C$9:$C$13)),ROW($C$9:$C$13)^0)>0)*$D$9:$D$13)

increase more student by increase range at blue

[Stever7]

Thanks to you both very much for the help

That seems to have done the trick and as far as I can see it works exactly as hoped Again, thank you

[Glenn Kennedy]

However, it will break down after 9 students. So, to repeat my Q. What is the maximum number of sudents in C3:C5?

[Stever7]

Apologies, I didn't realise that limitation and assumed the fix meant it was fully variable.

The maximum number of students is undefined, and could be as high as ~100 :/

[Glenn Kennedy]

100 in total, or 100 in any # delimited string? It's the number in any # delimited string that's important. Also, are the students lited by their name or by an ID nunber. If by a number, how many digits in each number?

[Stever7]

Sorry, was getting mixed up between projects (of which there could be up to around 100 but looking at the formula that doesn't seem to matter; right?). Student wise there could be up to around 20 (but maybe say 30ish to be safe?) and all of them may be in a given # delimited string

(Sorry again for my mix-ups!)

[Glenn Kennedy]

You missed one q. Student ids - name or number? If number, how many characters long? I'm away from PC, but will be back soon.

[Stever7]

Once again sorry (I promise I'm not doing this on purpose!!)

It's just numbers and they'll either be 1 or 2 digits

[samba_ravi]

Try below array formula in E3 and copy towards down, which is similar to Bo_Ry solution

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Stever7]

Hi Samba_ravi, that seems to be working (although after my previous oversights/mistakes I'm hesitant to say that too definitively!). On the assumption that it is, I do have an additional (and related) question you (or someone else) might be able to help with? The problem is such that:

• The same 'rules' apply as they did before
• However, there are other conditions attached to the project
• These conditions are things like "Subject" (I've included this in a new workbook which I've attached)
• Basically, when doing the totally, if the case subject matches the project subject then proceed as before; but if they don't match then ignore that project
• I anticipate 2 conditions are likely but I've included just the 1 in the example attached for simplicity on the assumption that I [I]should{/I] be able to adapt the solution to additional criteria. If additional criteria would drastically change the problem please just say and I'll alter the example

I would have asked along with my original question but I had hoped to be able to integrate that solution into the wider problem myself (having done something similar before); but having seen the solution I now realise I'm in a bit over my head on that front!

[Bo_Ry]

Please try at F3 and press Ctrl+Shift+Enter

=SUMPRODUCT((MMULT(--ISNUMBER(FIND(--MID(SUBSTITUTE("#"&C3,"#",REPT(" ",99)),TRANSPOSE(ROW($C$9:$C$13)-ROW($C$8))*99,99),$C$9:$C$13)),ROW($C$9:$C$13)^0)>0)*$E$9:$E$13*($D$9:$D$13=D3))

Additional criteria in Blue

Increase No 13 in red when you have more data.

[Stever7]

Brilliant; that seems to be exactly what I was after! I've modified it to include an additional criteria and it all seems to be working I'll do a few more tests now and go about integrate this solution into my main workbook (which is laid out slightly differently than my simplified/tidied example workbooks) to ensure everything continues to work as expected and then I'll mark this thread as Solved

Thank you to everyone who helped me; I really appreciate it

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Glenn Kennedy]

Still not there. Bo Ry's still breaks down after 5 students. I can understand why... there are too many references to C9:c13 and it's not always immediately obvious what role they are fulfilling.!!

use This array formula instead:

=SUMPRODUCT((MMULT(--ISNUMBER(FIND(--MID(SUBSTITUTE("#"&C3,"#",REPT(" ",99)),TRANSPOSE(ROW($A$1:$A$30))*99,99),$C$9:$C$13)),ROW($A$1:$A$30)^0)>0)*$E$9:$E$13*($D$9:$D$13=D3))

if you have more than 30 students, increase both 30s as needed.

[Stever7]

Hi Glenn, just a very quick reply to say I'm away from my PC for the rest of the day but I'll look to apply your solution tomorrow and report back

[Stever7]

Hi Glenn,

I've integrated your solution into my primary workbook and it seems to work perfectly! If I encounter any unforeseen limitations I'll reopen the thread but right now I think this completely solves my problem Thank you very much And thanks to everyone else who helped as well