Unable to set entire column active

[Mr_GZ]

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

 

[Scott Huish]

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]

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]

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]

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]

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.