SUNPRODUCT
 

I am using the code below to populate cell E20 on a sheet. It works fine
but I was wondering if there is a way to improve it.

[E20] =
[SUMPRODUCT((rng1=3)*(rng2={"Yes1","Yes2","Yes1^"," Yes2^","NI","NI^"})*IsBla
nk(rng3)*IsBlank(rng4)*IsBlank(rng5)*IsBlank(rng6) *IsBlank(rng7))]

rng3 to rng7 are adjacent columns.

Thanks in advance.

Gareth

SUNPRODUCT
 

What would you consider an improvement?

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
I am using the code below to populate cell E20 on a sheet. It works fine
but I was wondering if there is a way to improve it.

[E20] =

[SUMPRODUCT((rng1=3)*(rng2={"Yes1","Yes2","Yes1^"," Yes2^","NI","NI^"})*IsBla
nk(rng3)*IsBlank(rng4)*IsBlank(rng5)*IsBlank(rng6) *IsBlank(rng7))]

rng3 to rng7 are adjacent columns.

Thanks in advance.

Gareth

SUNPRODUCT
 

shorter code and not so many named ranges...

can Excel look at rng3 - rng7 as one range??

"Tom Ogilvy" wrote in message
...
What would you consider an improvement?

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
I am using the code below to populate cell E20 on a sheet. It works
fine
but I was wondering if there is a way to improve it.

[E20] =


[SUMPRODUCT((rng1=3)*(rng2={"Yes1","Yes2","Yes1^"," Yes2^","NI","NI^"})*IsBla
nk(rng3)*IsBlank(rng4)*IsBlank(rng5)*IsBlank(rng6) *IsBlank(rng7))]

rng3 to rng7 are adjacent columns.

Thanks in advance.

Gareth

SUNPRODUCT
 

I can't think of an easy way to get the same result using that approach. I
won't say there isn't a way, but I don't think it would make your formula
much smaller if there were.

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
shorter code and not so many named ranges...

can Excel look at rng3 - rng7 as one range??

"Tom Ogilvy" wrote in message
...
What would you consider an improvement?

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
I am using the code below to populate cell E20 on a sheet. It works
fine
but I was wondering if there is a way to improve it.

[E20] =



[SUMPRODUCT((rng1=3)*(rng2={"Yes1","Yes2","Yes1^"," Yes2^","NI","NI^"})*IsBla
nk(rng3)*IsBlank(rng4)*IsBlank(rng5)*IsBlank(rng6) *IsBlank(rng7))]

rng3 to rng7 are adjacent columns.

Thanks in advance.

Gareth

SUNPRODUCT
 

ta...

"Tom Ogilvy" wrote in message
...
I can't think of an easy way to get the same result using that approach.
I
won't say there isn't a way, but I don't think it would make your formula
much smaller if there were.

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
shorter code and not so many named ranges...

can Excel look at rng3 - rng7 as one range??

"Tom Ogilvy" wrote in message
...
What would you consider an improvement?

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
I am using the code below to populate cell E20 on a sheet. It works
fine
but I was wondering if there is a way to improve it.

[E20] =




[SUMPRODUCT((rng1=3)*(rng2={"Yes1","Yes2","Yes1^"," Yes2^","NI","NI^"})*IsBla
nk(rng3)*IsBlank(rng4)*IsBlank(rng5)*IsBlank(rng6) *IsBlank(rng7))]

rng3 to rng7 are adjacent columns.

Thanks in advance.

Gareth