Count question

[pyclen]

Hi All,

this is a question for XL2019 (work provided), so some of the fancy wizardry for 365 may not work.

I have two tables, one serves as input, the other is the calculation based on the input. these tables are on separate tabs but for this post I added them on the same

In Field C7 I have the number of samples that are being tested, can be any value from 1 - 10, the table on the right (K5:L10) lists the test and corresponding cost.

In the upper table I have the test being performed (B7-B11) and the timepoints from C6 - I6

In C7:I11 I have the pertinent info as it will inform if the sample is tested at Condition 1 (X), Condition 2 (Y), Condition 3 (Z) at any given time.
For example, Test 1 is performed for Condition 1 & 2 at 1, 2,3, and 6 months, and only at Condition 1 at 0, 9, 12 months.

In the lower table I have the same tests and times as before however here is where the money is calculated.
For times 0, 9 & 12 months, I can calculate the cost. Example C18 =

=$C$3*(LEN(C7)-LEN(SUBSTITUTE($C7,"X","")))*IF($C7="","$0",INDEX($L$6:$L$10,MATCH($B$18,$K$6:$K$10,0)))

However when I come to 1, 2, 3 or any timepoint really where I do have multiple conditions I am not able to count the characters, as I do need the Sum of X and Y for the calculation to be correct.
Example:

1Month, Test 1 has both X, Y, i.e., two (2) conditions, therefore the formula to calculate the correct value needs to include that otherwise I get a wrong result.
For this example it should be 2 (samples) * 2 (conditions) * $100 = $400, and not 200

I am hoping someone here may have a solution for this issue.


Thank you in advance for any help/guidance

 

[Anthony47]

What about counting the Commas?

=$C$3*(1+LEN(C7)-LEN(SUBSTITUTE(C7,",","")))*IF(C7="",0,INDEX($L$6:$L$10,MATCH($B18,$K$6:$K$10,0)))

 

[pyclen]

What about counting the Commas?

=$C$3*(1+LEN(C7)-LEN(SUBSTITUTE(C7,",","")))*IF(C7="",0,INDEX($L$6:$L$10,MATCH($B18,$K$6:$K$10,0)))

That is an innovative way of doing it however it may break down if there is no comma entered (like someone forgot)
Thanks for your response

 

[Anthony47]

So go and nest Substitute...
Not SUBSTITUTE(C7,"X","")
but

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(C7),"X",""),"Y",""),"Z","")

 

[pyclen]

Thank you, that works

appreciated

 

[Anthony47]

Thank you for the feedback
If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution

 

[Phuoc]

A little shorter:

=$C$3*VLOOKUP($B18,$K$6:$L$10,2,0)*COUNT(SEARCH({"X","Y","Z"},C7))

 

[pyclen]

THank you Phuoc, appreciate the shorter solution