# Unable to set entire column active

#### Mr_GZ

##### New Member
I am unable to set entire column A and B active with these two formulas

=SUMIF(Sheet2!\$A:\$A;Sheet1!B3;Sheet2!\$B:\$B)
=SUMPRODUCT((Sheet2!A5:\$A\$100=Sheet1!B3)*(Sheet2!B5:B100))

How do I solve this ?
Need it to work with one of these formula.

Sheet1:

Accounting entry_________ Cost centre _________ Amount
4121 _________ 566 _________ 11 532,00

Sheet2:

Cost centre _________ Amount
601 _________ \$512
566 _________ \$6 000
100 _________ \$1 500
566 _________ \$5 532
122 _________ \$3 550

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Scott Huish

##### MrExcel MVP
Formulas don't actually select or activate other cells.

But COUNTIF only has 2 parameters, the range and criteria, perhaps you want SUMIF.

You can't use whole columns with SUMPRODUCT. The maximum number of rows prior to version XL2007 is 65535.

#### Mr_GZ

##### New Member
Sorry I made a typo I meant SUMIF but wrote COUNTIF.

A:A and B:B does for some reason not work for me with SUMIF, why is that

#### Scott Huish

##### MrExcel MVP
My version uses commas instead of semicolons, and this worked for me:
=SUMIF(Sheet2!\$A:\$A,Sheet1!B3,Sheet2!\$B:\$B)

Do your numbers actually have spaces in them?

And if not, what is happening exactly when you put in the formula?

#### Mr_GZ

##### New Member
Strange, is there any difference between using commas instead of semicolons in a function?

My mate report to me he gets a #invalid! Error (not sure if it is the correct excel translation – we are using a non English version) in the function argument window in excel 2007. It does work properly other than that it seems though. I myself get none such error in the function argument window when I use A:A in the function – I am using excel 2000.
What is the best way to go to get what I want to achieve with the functions?
Can A:A really be used propperly in a formula as this ?

#### Scott Huish

##### MrExcel MVP
The American version of Excel uses commas, some (I don't know if all) international versions uses semicolons.

I don't have XL2007 but previous versions of Excel allowed whole column references in SUMIF formulas.

Replies
0
Views
80
Replies
3
Views
168
Replies
26
Views
406
Replies
1
Views
473
Replies
0
Views
352

1,181,372
Messages
5,929,565
Members
436,679
Latest member
helly123

### 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.

### Which adblocker are you using?

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

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