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.
Ashraf NomanAsked:
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.

Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
Filters just hide rows. That won't change the results of a formula which works on a range.
0
Ashraf NomanAuthor 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
Rgonzo1971Commented:
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)

Open in new window

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.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ashraf NomanAuthor Commented:
Thank you very much.  could you please give me the formula for D column also.
0
Rgonzo1971Commented:
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)))

Open in new window

1
Rob HensonFinance 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
Rob HensonFinance 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
Ashraf NomanAuthor Commented:
@Rgonzo1971 thanks dear.
0
Ashraf NomanAuthor 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
Rgonzo1971Commented:
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.