Sumif and changing array

[csimont]

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(R3793C,0,0))"

Thanks!

[Bob Phillips]

=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" <csimont@discussions.microsoft.com> wrote in message
news:BCA4AF66-3CFE-41DC-AFA7-7C927953354A@microsoft.com...
> 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(R3793C,0,0))"
>
> Thanks!

[csimont]

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(R3793C,0,0))"
>
> Thanks!

[Bob Phillips]

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" <csimont@discussions.microsoft.com> wrote in message
news:A4BC6ED2-EA4F-4480-86EE-F364453B219E@microsoft.com...
> 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(R3793C,0,0))"
> >
> > Thanks!