SUM text.

[ebeyert]

I have a row D37 to AA37 with fitness exercises (text). Table D18 to AA18 contains all fitness exercises (also text). Now I want to add in D49 the number of exercises (text) described in row D37 to AA37 that correspond in the table D1818 to AA18.. I am trying with count, countif, sumproduct, etc but nothing work.
Tkanks for helping me out
BEst regards
Ellerd

 

[felixstraube]

What condition has to be met to add the values? For example has de excercise in D18 to be the same as in D37 for it to count as 1? Or if excercise in D18 is in the list of excecises from D37:A37 then you count 1?

 

[felixstraube]

Something like this?:

Count if D18 = D37, E19 = E37,...

Libro1
	C	D	E	F	G	H
18	All Excercises	Excercise01	Excercise02	Excercise03	Excercise04	Excercise05
37	Some Excercises	Excercise01	Excercise04	Excercise03		Excercise05
49	Formula	3
Example 1

Cell Formulas
Range	Formula
D49	D49	=SUM((D18:H18=D37:H37)*1)

Or something like this?:
Counts 1 if excercise in row 37 appears in the list of all excercises

Libro1
	C	D	E	F	G	H
18	All Excercises	Excercise01	Excercise02	Excercise03	Excercise04	Excercise05
37	Some Excercises	Excercise02	Excercise01	Excercise03	Excercise03
49	Formula	4
Example 2

Cell Formulas
Range	Formula
D49	D49	=SUM(BYCOL(D37:H37,LAMBDA(x,SUM((D18:H18=x)*1))))

 

[ebeyert]

Felixstraube, Thanks for you reply. Your last formula is correct, because I have 4 x exercises in D37-H18, all 4 of which are also present in D18-H18. The only issue is that if I use your formula with empty cells, it will also add the empty cells?
Thanks
Ellerd

 

[FormR]

If the cells are truly empty, then you could try:

=SUM(--ISNUMBER(MATCH(D37:AA37,D18:AA18,0)))

If they might contain formula that returns "" then you could try:

=SUM(--ISNUMBER(MATCH(D37:AA37,FILTER(D18:AA18,D18:AA18<>""),0)))

The second option might also be more efficient if quite a few of the cells in row 18 are empty/"".

 

[ebeyert]

FormR, thanks for support and feedback. The second options works great !.
Thanks again
BEst regards
Ellerd