SUNPRODUCT

[Gareth]

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

 

[Tom Ogilvy]

What would you consider an improvement?

 

[Gareth]

shorter code and not so many named ranges...

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

What would you consider an improvement?

--
Regards,
Tom Ogilvy

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

 

[Tom Ogilvy]

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

shorter code and not so many named ranges...

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

What would you consider an improvement?

--
Regards,
Tom Ogilvy

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

 

[Gareth]

ta...

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

shorter code and not so many named ranges...

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

What would you consider an improvement?

--
Regards,
Tom Ogilvy

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