Filters and Subtotal(9,A1:A1000)

[gheyman]

Is there a way to create the type of formual that "Subtotal" is (Where it does not include filtered rows in its total) with a SumIf Formula?

I want the SumIf formula to only add the values where its corresponding cell in column E = Yes. But I dont want the total to include the row where E is Yes, but the column is hidden.

Hope thats clear enough.

Thanks
Greg

 

[schielrn]

See if this is what you are looking for and can adapt it:

http://www.excelforum.com/excel-general/680495-subtotal-sumif-on-autofilter-visible-cells-only.html

also here:

http://www.ozgrid.com/forum/showthread.php?t=32446&page=1

I have never done it, but if you can't get it to work post back adn I can look at it too.

Hope that helps.

 

[Aladin Akyurek]

Is there a way to create the type of formual that "Subtotal" is (Where it does not include filtered rows in its total) with a SumIf Formula?

I want the SumIf formula to only add the values where its corresponding cell in column E = Yes. But I dont want the total to include the row where E is Yes, but the column is hidden.

Hope thats clear enough.

Thanks
Greg

Which range do you want to sum while AutoFilter is on?

 

[gheyman]

I am looking down column E9:E1000 for "Yes" and totaling value from column G9:G1000

I just used A as an example in my explaination. E and G are the actual columns being used in the formula

 

[Aladin Akyurek]

I am looking down column E9:E1000 for "Yes" and totaling value from column G9:G1000

I just used A as an example in my explaination. E and G are the actual columns being used in the formula

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(G9,ROW(G9:G1000)-ROW(G9),,1)),--(E9:E1000="Yes"))

 

[gheyman]

Thanks Very much.

Both samples worked. Two approaches and both come up with the same answer.

I need to do my homework on SUMProduct.