ranges to repeat

[vinod9111]

Is there any short cut for repeating the ranges in a formula( array).
for instance in the below formuala i have to repeat the range E3:E5

=SUMPRODUCT(--(E3:E5="d")+(--(E3:E5="v")))

any ideas on this

 

[Colin Legg]

It will be specific to the formula, but in the example you gave you can improve its efficiency and shorten it by using this instead:

=SUM(COUNTIF(E3:E5,{"d","v"}))

By the way, in your Sumproduct formula, because you are adding the Boolean result arrays, you don't need the negation:

=SUMPRODUCT((E3:E5="d")+(E3:E5="v"))

 

[Kaps_mr2]

used a named range but it will probably be more characters than the original text. Thks

Kaps