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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,875
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,875
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
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,887
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,171,245
Messages
5,874,634
Members
433,063
Latest member
Greg_77140

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