Changing my functions to use subtotals?
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"cherman" wrote in message
...
That was the ticket. Just a couple minor changes and bam!
Much thanks!!!
"T. Valko" wrote:
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(D2:D15,ROW(D2:D15)-ROW(D2),0,1)),--(A2:A15=I5601),--(E2:E15=1))
--
Biff
Microsoft Excel MVP
"cherman" wrote in message
...
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
.
|