Array formula

[mbtaichi]

Hi ,
I have this formula created with the help of this message board, thank you.

=SUM(COUNTIF($B6,"*"&{"M","TU","W","TH","F","SA"}&"*")*{7.42,7.08,8.5,8.45,8.1,7.25})

I was wondering if it is possible to but not have the values that are added hard coded into the formula but put into individual cells that the formula could look in.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]7.42[/TD]
[TD]7.08[/TD]
[TD]8.5[/TD]
[TD]8.45[/TD]
[TD]8.1[/TD]
[TD]7.25[/TD]
[/TR]
</tbody>[/TABLE]

That way i could change the values when needed

Thank you for your help

 

[Eric W]

Here are a couple of options:

Book1
	B	C	D	E	F	G	H	I
6	Thursday	8.45
7		8.45	7.42	7.08	8.5	8.45	8.1	7.25
8		8.45
Sheet1

Cell Formulas
Range	Formula
C6	=SUM(COUNTIF($B6,"*"&{"M","TU","W","TH","F","SA"}&"*")*{7.42,7.08,8.5,8.45,8.1,7.25})
C7	=SUMPRODUCT(COUNTIF($B6,"*"&{"M","TU","W","TH","F","SA"}&"*")*D7:I7)
C8	=LOOKUP(2,1/(SEARCH({"M","TU","W","TH","F","SA"},B6)),D7:I7)

Your formula is in C6, the formula with a range is in C7, and if your B6 cell can only have 1 date (not something like "Wednesday, Thursday"), then the C8 formula should work too.

 

[mbtaichi]

Thank you