Range Name in Arrary Formula

ramana

New Member
Joined
Oct 9, 2006
Messages
4
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]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 9, 2006
Messages
4
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 9, 2006
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

New Member
Joined
Oct 9, 2006
Messages
4
Hi PGC and Jhon

thanks for your suggestions. I appreciate your help.

Have a good day

Regards

Ramana
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
It's funny how you can't use whole columns in array formulae or Sumproducts but you can in Sumifs.

Richard
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top