Custom Formula with Specific Functions

[KhmerBoi1]

I'm trying to assign values to specific characters then aggregate and summarize them. I was trying to use specific functions, SUM or SUMPRODUCT and CHOOSE.

For instance:
If you have a row as depicted below. Each column is a DAY within the month in which the person decided to carpool. I want to calculate the amount of One-Way Trips.
X = Round Trip (2 One-Way Trip) // M = Morning ONLY (1 One-Way Trip) // E = Evening ONLY (1 One-Way Trip) // Any Other Character is valued as a 0.

A	B	C	D	E	F	G	H	I	J	K	L
X	M	E	X	X	DP	X	X	X	X		16

The amount of One-Way Trips for the above would be 16.

Is there a way to use SUM or SUMPRODUCT and the CHOOSE functions to accomplish this? The CHOOSE function would be CHOOSE(value, {0, 1, 2}) or something similar. I'm not sure how to loop through each column in the row and summarize each cell in the ROW range.

Thank you for your help ahead of time.

 

[dreid1011]

Perhaps this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	X	M	E	X	X	DP	X	X	X	X		16
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUMPRODUCT(((A1:J1="X")*2)+(A1:J1="M")+(A1:J1="E"))

 

[KhmerBoi1]

Perhaps this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	X	M	E	X	X	DP	X	X	X	X		16
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUMPRODUCT(((A1:J1="X")*2)+(A1:J1="M")+(A1:J1="E"))

Was I thinking about it wrong to try to use the CHOOSE function?

Would that have been overkill?

Not wanting to be picky, but while there is not specific reason, could the CHOOSE function have been used?

 

[dreid1011]

Was I thinking about it wrong to try to use the CHOOSE function?

Would that have been overkill?

Not wanting to be picky, but while there is not specific reason, could the CHOOSE function have been used?

I'm not sure, I'll have to play with it some.

 

[dreid1011]

You could use CHOOSE like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	X	M	E	X	X	DP	X	X	X	X		16
2	X	M	E	X	X	DP	X	X	X	X		16
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUMPRODUCT(((A1:J1="X")*2)+(A1:J1="M")+(A1:J1="E"))
L2	L2	=SUM(IFERROR(CHOOSE(LOOKUP(A2:J2,{"E","M","X"},{1,2,3}),1,1,2),0))

Though with the LOOKUP, you can get incorrect results if you have other single letters in the range. What version of Excel are you using?

 

[KhmerBoi1]

You could use CHOOSE like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	X	M	E	X	X	DP	X	X	X	X		16
2	X	M	E	X	X	DP	X	X	X	X		16
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUMPRODUCT(((A1:J1="X")*2)+(A1:J1="M")+(A1:J1="E"))
L2	L2	=SUM(IFERROR(CHOOSE(LOOKUP(A2:J2,{"E","M","X"},{1,2,3}),1,1,2),0))

Though with the LOOKUP, you can get incorrect results if you have other single letters in the range. What version of Excel are you using?

I'm using Microsoft Excel 2016 and newer. Sometimes Excel O365 through either the Web or TEAMS.

There is other single character letters but everything except "X, M, E" is valued as a 0.

 

[dreid1011]

Gotcha. Well when you use 365, you can use the option in L3, otherwise, I would stick with the sumproduct in L1 because it ignores those other values already. The lookup in L2 will try to match those values either way and return incorrect results.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	X	M	E	X	X	DP	X	X	X	X		16
2	X	M	E	X	X	DP	X	X	X	O		15	should be 14, but LOOKUP uses an approximate match
3	X	M	E	X	X	DP	X	X	X	O		14
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUMPRODUCT(((A1:J1="X")*2)+(A1:J1="M")+(A1:J1="E"))
L2	L2	=SUM(IFERROR(CHOOSE(LOOKUP(A2:J2,{"E","M","X"},{1,2,3}),1,1,2),0))
L3	L3	=SUM(IFERROR(CHOOSE(XLOOKUP(A3:J3,{"E","M","X"},{1,2,3},0,0,1),1,1,2),0))

 

[KhmerBoi1]

Gotcha. Well when you use 365, you can use the option in L3, otherwise, I would stick with the sumproduct in L1 because it ignores those other values already. The lookup in L2 will try to match those values either way and return incorrect results.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	X	M	E	X	X	DP	X	X	X	X		16
2	X	M	E	X	X	DP	X	X	X	O		15	should be 14, but LOOKUP uses an approximate match
3	X	M	E	X	X	DP	X	X	X	O		14
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUMPRODUCT(((A1:J1="X")*2)+(A1:J1="M")+(A1:J1="E"))
L2	L2	=SUM(IFERROR(CHOOSE(LOOKUP(A2:J2,{"E","M","X"},{1,2,3}),1,1,2),0))
L3	L3	=SUM(IFERROR(CHOOSE(XLOOKUP(A3:J3,{"E","M","X"},{1,2,3},0,0,1),1,1,2),0))

Thank you!

 

[dreid1011]

You're welcome, and happy to help.