Changing my functions to use subtotals?

[cherman]

I have 2 functions that are calculating my data correctly, except now I need
to consider the autofilter. I figure using subtotal is the way to go if I
want to disregard filtered out rows. Also, I plan on moving my data to
another sheet, so it will need to reference the current sheet, which is
AAT_Raw_Data. Finally, the # of rows of data will vary, but I don't think it
will ever be over 10,000. I'm using Excel 2003, so I think there's an issue
with using something like "A:A" in my formulas.

Can someone help me change these to add in a subtotal and take into account
the other things I mentioned?

FORMULA 1: SUMIF(A:A,I5601,C:C)

FORMUA 2: SUMPRODUCT(--($A$1:$A$9995=I5601),--(E$1:E$9995=1),($D$1:$D$9995))

Much Thanks!
Clint

 

[Eduardo]

Hi,
Excel 2003 doesn't support to have ranges like A:A you need to specify the
row number

to get subtotal with filters the formula to be used is

=subtotal(9,A1:A30000)

when you filter the total will be updated to the filtered data

to copy filtered data to other sheet do this

once filtered hightlight the range, click CTRL G, Special, visible cells
only, OK, CTRL C, go to where you want to copy the information and paste

if this helps please click yes thanks

 

[cherman]

Thanks for your reply! I appreciate the time.

Can you write out what my new formulas would be? I need to integrate the
subtotal into both my current formulas listed below and I cannot figure
either of them out. I also need the sheet reference I mentioned below. I have
no problems using A1:A10000 instead of A:A.

Thanks again!

 

[Eduardo]

Hi,
The formula below are based in conditions to be met, for example if column A
is equal to cell I5601 you want to sum column F. in this case I don't see why
you want to apply filters. Despite that if you want to apply filters you will
have to add this formula in another cell

=subtotal(9,$c$1:$C$10000)

if you want to have totals of sheet called AAT_Raw_Data use

=subtotal(9,AAT_Raw_Data!$C$1:$C$10000)

 

[T. Valko]

Try these...

Let's assume the full unfiltered range is row 2 to row 15.

FORMULA 1: SUMIF(A:A,I5601,C:C)
=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2:C15,ROW(C2:C15)-ROW(C2),0,1)),--(A2:A15=I5601))

FORMUA 2:
SUMPRODUCT(--($A$1:$A$9995=I5601),--(E$1:E$9995=1),($D$1:$D$9995))

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D215,ROW(D215)-ROW(D2),0,1)),--(A2:A15=I5601),--(E2:E15=1))

 

[cherman]

That was the ticket. Just a couple minor changes and bam!

Much thanks!!!

 

[T. Valko]

You're welcome. Thanks for the feedback!