Range Name in Arrary Formula

[ramana]

Hi friends,

I named colum A entirly as "ColA" and column B entirly as "ColB". I used these range names in one of the arrary formula like follows:

{=sum(if(ColA="A", if(ColB="B", 1,0),0))}

But it returns #NUM! error. Please help how to resolve this error. I thought range names work very well in arrary formulas

Ramana[/url]

 

[Jon von der Heyden]

Hi,

Maybe it should be =SUMPRODUCT(--(ColA="A"),--(ColB="B"))?

Note, these named ranges should not be whole columns (i.e. A:A), array formula won't support these. Be specfic to the cell references, e.g. A1:A1000.

Hope this helps.

Jon

 

[ramana]

Thanks Jon. those two columns contain "Text" type data and not come under sumproduct.

But i thought arrary formulas support range names

Thanks for your quick response.

 

[Jon von der Heyden]

Sorry, you're not making sense to me, why won't SUMPRODUCT() work?

For an array formula this should work:
=SUM(IF((ColA="a")*(ColB="b"),1,0))
confirmed with Ctrl+Shift+Enter

Can you tell me what exactly ColA and ColB is? I.e the address?

Cheers,
Jon

 

[ramana]

ColA and ColB contains texual values like "Pharma", "Telecom" etc.

So iam wondering how sumproduct work here. I will try it anyway.

Thanks once again for helping me

 

[pgc01]

Hi ramana

You have 2 errors in your formula.

- SUMIF only accepts one criterion and you have 2.

- You can never use a whole column in an array formula

You can use a formula like Jon's but specifying a limited array, like:

=SUMPRODUCT(--(A1:A1000="A"),--(B1:B1000="B"))

or
=SUM(IF(A1:A1000="A",IF(B1:B1000="B",1)))
confirmed with CTRL+SHIFT+ENTER

Of course you can redefine ColA and ColB to a limited (equal) range and use them in the formulas.

Hope this helps
PGC

 

[ramana]

Hi PGC and Jhon

thanks for your suggestions. I appreciate your help.

Have a good day

Regards

Ramana

 

[Richard Schollar]

It's funny how you can't use whole columns in array formulae or Sumproducts but you can in Sumifs.

Richard