Ashraf Noman
asked on
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.
Brian B
Filters just hide rows. That won't change the results of a formula which works on a range.
ASKER
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
Subtotal-and-sumif.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. could you please give me the formula for D column also.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Rows that are hidden by physically hiding the row or collapsing a group will still affect the result.
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
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
ASKER
@Rgonzo1971 thanks dear.
ASKER
@Rob Henson..... thanks for your reply but I already know this way . Please have a look on @ Rgonzo1971 comment which help me my requirements.
Solutions according to author's comment