I came upon this formula with a 'record macro'. It worked for what I needed
to get done, but could someone break it down for me because it's confusing
and I'd like to use it for another report and also to expand to at least two
more columns with different criteria.

Basically what I did was, put my cursor on the field where the results would
go, and =countif, went into the second workbook to column P, selected the
column and typed in the criteria. It worked fine, I'm trying to do this for
another report and can't figure out what this formula really reads like. The
confusing part is the what's in between the brackets [] and that it has 'C'
instead of 'P', which is where the info came from. Thanks in advance.

Range("E6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[11],""Austria"")"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[10],""Belgium"")"
Range("G6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[9],""Germany"")"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[8],""Ireland"")"
Range("I6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[7],""Italy"")"
Range("J6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[6],""Luxembourg"")"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[5],""Netherlands"")"
Range("L6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[4],""Switzerland"")"

Your formula is in R1C1 mode meaning that it uses the relative row and column
number to address your cells. C[11] is column 11. So column P is 11 columns
over from cell E6...
--
HTH...

Jim Thomlinson


"CV323" wrote:

I came upon this formula with a 'record macro'. It worked for what I needed
to get done, but could someone break it down for me because it's confusing
and I'd like to use it for another report and also to expand to at least two
more columns with different criteria.

Basically what I did was, put my cursor on the field where the results would
go, and =countif, went into the second workbook to column P, selected the
column and typed in the criteria. It worked fine, I'm trying to do this for
another report and can't figure out what this formula really reads like. The
confusing part is the what's in between the brackets [] and that it has 'C'
instead of 'P', which is where the info came from. Thanks in advance.

Range("E6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[11],""Austria"")"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[10],""Belgium"")"
Range("G6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[9],""Germany"")"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[8],""Ireland"")"
Range("I6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[7],""Italy"")"
Range("J6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[6],""Luxembourg"")"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[5],""Netherlands"")"
Range("L6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ISCE!C[4],""Switzerland"")"

Ok makes sense now. I was confused on that C.

I realized that I got the info while the sheets were on the same workbook.

What if the info is in another workbook? It comes back saying that I
entered too few arguments. Is it not possible to "=countif" with the data in
two separate workbooks?

Did you record a macro to add the reference to the new workbook or did you
try to modify the code that you have? In either case post the code that you
have...

If you want to do a CountIf to a seperate workbook I think you might run
into an issue if the externally referenced workbook is closed because CountIf
doe not like referencing closed books. Take a look at this web site...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"CV323" wrote:


Ok makes sense now. I was confused on that C.

I realized that I got the info while the sheets were on the same workbook.

What if the info is in another workbook? It comes back saying that I
entered too few arguments. Is it not possible to "=countif" with the data in
two separate workbooks?