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?
 
Rgonzo1971Connect With a Mentor 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)

Open in new window

Regards
Subtotal-and-sumifV1.xlsx
1
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.