Sumif and changing array
 

I would love some help!!!!
I am trying to write a macro that would sumif data in column 12 based on
criteria in column 1 but with a different number of rows in different
worksheets. Within each worksheet, the data in column 1 would be grouped
(i.e. by fiscal year) and the macro for the sumif would subtotal each group.

Year Clients
FY2007 5
FY2006 15
FY2005 25
FY2007 12
FY2006 28
FY2005 7

Macro would give back:
FY2007 17
FY2006 43
FY2005 32

The next worksheet would have a the same data but more or less rows to
calculate.
This is what I have so far:
ActiveCell.FormulaR1C1 =
"=SUMIF(R8C1:OFFSET(R3793C1,0,0),RC10,R8C:OFFSET(R 3793C,0,0))"

Thanks!

Sumif and changing array
 

=SUMIF(A:A,"FY2007",B:B)

as SUMIF can take whole colums, the number of rows is immaterial

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"csimont" wrote in message
...
I would love some help!!!!
I am trying to write a macro that would sumif data in column 12 based on
criteria in column 1 but with a different number of rows in different
worksheets. Within each worksheet, the data in column 1 would be grouped
(i.e. by fiscal year) and the macro for the sumif would subtotal each
group.

Year Clients
FY2007 5
FY2006 15
FY2005 25
FY2007 12
FY2006 28
FY2005 7

Macro would give back:
FY2007 17
FY2006 43
FY2005 32

The next worksheet would have a the same data but more or less rows to
calculate.
This is what I have so far:
ActiveCell.FormulaR1C1 =
"=SUMIF(R8C1:OFFSET(R3793C1,0,0),RC10,R8C:OFFSET(R 3793C,0,0))"

Thanks!

Sumif and changing array
 

Bob:
thanks for your response! It works better than what I had tried. One problem:
How do I specify that the data in column L might have sections made up of
varying row numbers that need to be subtotaled. One worksheet might have
three sections made up of 15 rows, 200 rows, and 27 rows respectively. The
next worksheet might have two sections made up of 500 lines and 3 lines
respectively. The sumif formula works except for the designation for the
second range:

=SUMIF($A:$A,$J1,L:L) The L:L picks up all the data in column L despite the
breaks in the worksheet.

"csimont" wrote:

I would love some help!!!!
I am trying to write a macro that would sumif data in column 12 based on
criteria in column 1 but with a different number of rows in different
worksheets. Within each worksheet, the data in column 1 would be grouped
(i.e. by fiscal year) and the macro for the sumif would subtotal each group.

Year Clients
FY2007 5
FY2006 15
FY2005 25
FY2007 12
FY2006 28
FY2005 7

Macro would give back:
FY2007 17
FY2006 43
FY2005 32

The next worksheet would have a the same data but more or less rows to
calculate.
This is what I have so far:
ActiveCell.FormulaR1C1 =
"=SUMIF(R8C1:OFFSET(R3793C1,0,0),RC10,R8C:OFFSET(R 3793C,0,0))"

Thanks!

Sumif and changing array
 

if it has three sections of 15 lines, what do you want it to use?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"csimont" wrote in message
...
Bob:
thanks for your response! It works better than what I had tried. One
problem:
How do I specify that the data in column L might have sections made up of
varying row numbers that need to be subtotaled. One worksheet might have
three sections made up of 15 rows, 200 rows, and 27 rows respectively. The
next worksheet might have two sections made up of 500 lines and 3 lines
respectively. The sumif formula works except for the designation for the
second range:

=SUMIF($A:$A,$J1,L:L) The L:L picks up all the data in column L despite
the
breaks in the worksheet.

"csimont" wrote:

I would love some help!!!!
I am trying to write a macro that would sumif data in column 12 based on
criteria in column 1 but with a different number of rows in different
worksheets. Within each worksheet, the data in column 1 would be grouped
(i.e. by fiscal year) and the macro for the sumif would subtotal each
group.

Year Clients
FY2007 5
FY2006 15
FY2005 25
FY2007 12
FY2006 28
FY2005 7

Macro would give back:
FY2007 17
FY2006 43
FY2005 32

The next worksheet would have a the same data but more or less rows to
calculate.
This is what I have so far:
ActiveCell.FormulaR1C1 =
"=SUMIF(R8C1:OFFSET(R3793C1,0,0),RC10,R8C:OFFSET(R 3793C,0,0))"

Thanks!