Summing a Range Where Column Header Appears in a Concatenated Cell

[AliGW]

I am sure I could work this out in time, but I've run out of it today!

This should be copyable into a workbook for anyone who's willing to help. I am sure there's a new dynamic array function or two that would help.

Excel 2016 (Windows) 32 bit

	V	W	X	Y	Z	AA	AB	AC	AD	AE
8	A	B	C	D	E	F	Total	Groups	Blocks	No Fits
9	29						29	1	A	0
10	19	21	11	2			53	3	A,B,C	2
11	13	1	1	18			33	2	A,D	2

Sheet: GCSE Options 2021

I'm trying to think of a way of shortening this:

Excel 2016 (Windows) 32 bit

	AE
9	=IF(AND(ISERROR(FIND(V$8,$AD9)>0),V9<>""),V9,0)+IF(AND(ISERROR(FIND(W$8,$AD9)>0),W9<>""),W9,0)+IF(AND(ISERROR(FIND(X$8,$AD9)>0),X9<>""),X9,0)+IF(AND(ISERROR(FIND(Y$8,$AD9)>0),Y9<>""),Y9,0)

Sheet: GCSE Options 2021

It's the cells in red with white text that need summing - these are the choices that are out of block (that is not in a block that has been allocated in column AD). Cells in columns V to Y (which are the ones I'm interested in) contain formulae which return a value or a blank.

I'm at work, so can't prepare a sample workbook now, but could later if deemed necessary.

Thanks!

[XLent]

one option:

=SUMPRODUCT(ISERR(SEARCH(","&V8:AA8&",",","&AD9:AD11&","))+0,V9:AA11)
would return 4

edit: if this is for AE9:AE11 then

AE9: =SUMPRODUCT(ISERR(SEARCH(","&$V$8:$AA$8&",",","&$AD9&","))+0,$V9:$AA9)
copied down (would return 0, 2, and 2)

[AliGW]

That's clever, but not actually quite what I need, however I think I can adapt it - thanks! Will report back.

[AliGW]

Thanks again. This works for what I need:

=SUMPRODUCT(ISERR(SEARCH(","&$V$8:$Y$8&",",","&$AD9&","))+0,$V9:$Y9)

I'm curious to see if there's a solution using any of the new 365 functions, though, but this is much more streamlined than my original 4-parter!!!

[Fluff13]

How about

Please Login or Register  to view this content.

[AliGW]

I’m going to try this one in the morning - thank you!

[Fluff13]

My pleasure & thanks for the feedback.