Unable to set entire column active

Mr_GZ

New Member
Joined
Aug 1, 2007
Messages
16
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Aug 1, 2007
Messages
16
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Aug 1, 2007
Messages
16
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

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