# Sumif formula should work according to fillter

I want to use subtotal with sum if conditions. I mean to say when I should filter my sumif should change accordingly.
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EE Topic Advisor, Independant Technology ProfessionalCommented:
Filters just hide rows. That won't change the results of a formula which works on a range.
0
Author Commented:
you mean formula will not work. For your reference I attached the excel. could you please check and revert be in same excel. thanks.
Subtotal-and-sumif.xlsx
0
Commented:
Hi,

pls try in C3 and fill down
``````=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(\$B\$12:\$B\$49,1,1),ROW(\$B\$12:\$B\$49)-ROW(INDEX(\$A\$12:\$B\$49,1,1)),0))=1),--(\$B\$12:\$B\$49=B3),\$C\$12:\$C\$49)
``````
Regards
Subtotal-and-sumifV1.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thank you very much.  could you please give me the formula for D column also.
0
Commented:
then try
``````=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(\$B\$12:\$B\$49,1,1),ROW(\$B\$12:\$B\$49)-ROW(INDEX(\$A\$12:\$B\$49,1,1)),0))=1),--(\$B\$12:\$B\$49=B3),SIGN(ROW(\$B\$12:\$B\$49)))
``````
1
Finance AnalystCommented:
SUBTOTAL does not include values from rows hidden by a filter so just applying the filter WILL change the result of the SUBTOTAL.

Rows that are hidden by physically hiding the row or collapsing a group will still affect the result.
0
Finance AnalystCommented:
Ignore my comment, although correct I hadn't looked at the file and it is not valid.

However, looks like you can achieve what you want with a Pivot Table.

See attached. On Pivot sheet, use dropdown in B1 to select the Retail Environment on which to filter.
Subtotal-and-sumif.xlsx
0
Author Commented:
@Rgonzo1971 thanks dear.
0
Author Commented:
@Rob Henson..... thanks for your reply but I already know this way .  Please have a look on @ Rgonzo1971 comment which help me my requirements.
0
Commented:
Solutions according to author's comment
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.