Formula with curly brackets not working

[Nick70]

Hi,

I have a really tough one here so I would be over the moon if anyone has an answer to it.

A colleague of mine has created a s/s where there is a formula (see below) which basically looks at text in a range (C110:AF110) and if it contains certain words e.g. completed or Initial Review etc. it will count it as 1 or 1*weight e.g. 1*0.15 or 1*0.25 (see below) and add all these numbers together and then divide the total by 20.

=(SUM(COUNTIF(C110:AF110,{"completed","N.A."}))+(SUM(COUNTIF(C110:AF110,"Initial Review")*0.15))+(SUM(COUNTIF(C110:AF110,"reviewing")*0.25))+(SUM(COUNTIF(C110:AF110,"reworks")*0.75))+(SUM(COUNTIF(C110:AF110,"follow up")*0.5))+(SUM(COUNTIF(C110:AF110,{"requested","*week*"})*0.25)))/20

What is really strange is that the formula works for him but does not work for me even though we look at the same excel file!

When he changes text 'Initial review' or 'reviewing' to 'completed' for example, the final number changes (because completed counts as 1 in formula whereas initial review and reviewing count as 0.15 and 0.25 respectively).

I am wondering if his Excel has some add-ins or something which mine does not and for this reason this formula with curly brackets works for him but not for me?

Does anyone have any possible suggestions as to how this may be possible (i.e. formula works for him when he opens file in excel but not for me when I open same file in excel)?

Could it be that because of curly brackets I need something special in my excel to be able to read formula?
Could it be that if he created the array formula then it will work for him but not for me?

Thanks,
Nic

 

[jasonb75]

There are a few extra functions in there that are not needed, but nothing out of the ordinary and certainly no obvious reason for it to work for one user but not another.
Compressing the formula a little, although I don't see it making any difference.

=SUMPRODUCT(COUNTIF(C110:AF110,{"completed","N.A.","Initial Review","reviewing","reworks","follow up","requested","*week*"}),{0,0,0.15,0.25,0.75,0.5,0.25,0.25})/20

 

[Fluff]

@jasonb75 shouldn't the 2nd array start with 1,1,0.15, rather then 0,0,0.15 ?

 

[Nick70]

Hi Jason,

Thanks a lot for improving formula.

I have now looked again at whether formula works or not and surprise, surprise it works!
The old formula that is (but I will later change it to yours).

As I am working from home in remote maybe that had some kind of effect on excel performance which now has disappeared.

Thanks a lot anyway.

Nix

 

[Dave Patton]

Another idea to consider.
This is a mini version of your challenge.
N.B. You can post an extract of your sheet with the forum's tool XL2BB.
The Lookup can use a table or be included in the formula.

T202110a.xlsm
	B	C	D	E	F	G	H
13
14						Competed	1
15						Follow up	0.5
16						Initial Review	0.15
17						Reviewing	0.25
18						Reworks	0.75
19
105
106
107	0.1925	0.1925	0.1925
4a

Cell Formulas
Range	Formula
B107	B107	=(SUM(COUNTIF(C110:K110,{"completed","N.A."}))+(SUM(COUNTIF(C110:K110,"Initial Review")*0.15))+(SUM(COUNTIF(C110:K110,"reviewing")*0.25))+(SUM(COUNTIF(C110:K110,"reworks")*0.75))+(SUM(COUNTIF(C110:K110,"follow up")*0.5))+(SUM(COUNTIF(C110:K110,{"requested","*week*"})*0.25)))/20
C107	C107	=SUMPRODUCT(LOOKUP(C110:K110,G14:H18))/20
D107	D107	=SUMPRODUCT(LOOKUP(C110:K110,{"Competed",1;"Follow up",0.5;"Initial Review",0.15;"Reviewing",0.25;"Reworks",0.75}))/20

 

[jasonb75]

@jasonb75 shouldn't the 2nd array start with 1,1,0.15, rather then 0,0,0.15 ?

I should know better than to try and write formulas when I'm tired