Subtotal with If (thought it was pretty straightforward but guess not!)

[greentea]

Hi guys,

I'm trying to create a subtotal if Column A = 2017 and the formula seems pretty simple in my mind but guess not!

=IF(A:A=2017,SUBTOTAL(9,BH:BH), 0)

Help?

Thanks!

[AliGW]

Why SUBTOTAL? Why not SUMIF?

=SUMIF(A:A,2017,BH:BH)

[greentea]

Sorry, I should have clarified I am referencing a table with slicers and only want to captured filtered data.

[CK76]

You could use SUMPRODUCT(SUBTOTAL(,OFFSET())) construct.
Make sure that you don't use entire range, but only the range that encompass table range.

See link for how it is used. If you need help in writing formula, please upload sample workbook.
link removed

EDIT: Detailed explanation of how the formula works is found below.
https://chandoo.org/wp/formula-foren...ltered-tables/

Another method is to add helper column that serves to indicate if row is filtered or not.
Ex: in BI
=SUBTOTAL(102,BH2)
Copy down.

Then use SUMIFS() checking for BI:BI = 1.

[AliGW]

Please note rule 13:

• Links in posts must be to relevant free solutions on your site.
• Links in signatures must be to landing site of your blog that clearly promotes the free material above the commercial offerings.
  
• Cross-promotion of, or links to, forums competitive to this forum in signatures or posts is prohibited.

The Chandoo link is permissible as it's a tutorial, but the Mr Excel link to a forum thread is not.

[greentea]

Here is my sample workbook. Thanks again for your help!!!

[CK76]

Thanks for the heads up Ali. Noted.

[joycoco]

note i changed label in row 11.

[CK76]

See attached. I wasn't able to post formula due to firewall. See D18 to O20.

Note: Table1[[Period]:[Period]] is used to make table structured reference absolute (i.e. does not change when copied across).

[Richard Buttrey]

Try in D12

Please Login or Register  to view this content.

and ditto elsewhere.

I suggest you tidy up your data. At the moment column A has both '2017' and '2018 BUD'....etc.
Keep like things in their own column. So add an extra column for Type of item e.g. BUD, LE LY...whatever and keep column A just for a date/year..whatever.

If you stat mixiing up dissimilar things in the same column you'll find yourself jumping through all sorts of hoops.

[CK76]

Woops, I had error in the formula.

In D18, AU$30 should be changed to AU$29. And then copied down and across.