Is there a way to have cell equal to autofilter?

[Aladdin Sane]

I have 2 worksheets, and I was wondering if I can have a cell from Sheet2 equal to the subtotal of the Autofilter criteria of a column in Sheet1.
I do have something working with macros but it takes a long time. This would make things a lot easier.

Thanks

[NBVC]

You mean something like:

=Subtotal(9,Sheet1!A:A)

which will sum filtered data in column A of Sheet1

[Aladdin Sane]

Yes, but then I want different cell on sheet2 to be the subtotal of the same column but with a different autofilter criteria.

[NBVC]

See here:

http://www.contextures.com/xlFunctions04.html#Visible

[Aladdin Sane]

Thanks for the help, but this still requires you to manually change the autofilter criteria. Is there any way the cell can filter it by itself?

[NBVC]

You can't automatically change the autofilter criteria unless you use VBA as you already have done....

... do you know about the Countif() and Sumif() functions.... these count and sum based on specific criteria...maybe this is really what you want?

e.g. =Countif(Sheet1!A:A,A2) will count items in column A of Sheet 1 that have the criteria you put in A2 of your active sheet.

likewise, =Sumif(Sheet1!A:A,A2,Sheet1!B:B) will sum items in B based on criteria as I described with the Countif above....

[Aladdin Sane]

Do you know how to do COUNTIF with multiple conditions? Been trying it for a while.

[NBVC]

If you have 2007, it's Countifs()

If you don't have 2007, you have to use Sumproduct() for multiple conditions...

e.g. =Sumproduct(--(A1:A100=Condition1),--(B1:B100=Condition2))

you can continue adding conditions under the same pattern.

[Aladdin Sane]

It appears the sumproduct function is not working properly.
So I tried finding out whats wrong.
When I write
=Sheet1!C6:C2610=A2 in a cell i get the #VALUE! error.
When I write
=Sheet1!C:C=A2 in a cell is says FALSE but the value in A2 is in that range (column C) of sheet 1.

[NBVC]

What do you mean by it's not working properly?

Do you get an error (which one)? - check for errors in the range....

Do you get wrong result? - check that values in Column C match exactly what's in A2.... formats must be same and no extra spaces, etc...

If you still have problem, please post sample zipped sheet (excel 2003 or earlier please).