Using SUMIF with filtered columns

[philiphales]

We (hopefully) know how to use SumIf, but what if I have filtered columns, instead of sum I can use SubTotal, but I want a "SubTotalIf".

Can anyone help, thank you.

 

[Chihiro]

Use following construct.
=SUPRODUCT(SUBTOTAL(,OFFSET())*(ConditionRange = "Condition"))

You can find example usage in link below.
https://social.technet.microsoft.co...tal-or-nesting-subtotal-in-sumifs?forum=excel
http://chandoo.org/wp/2014/07/23/formula-forensics-no-037-how-to-count-and-sum-filtered-tables/

Another method is to use helper column. You can find detail below.
http://chandoo.org/wp/2010/05/11/exclude-hidden-rows-from-totals/

 

[philiphales]

Hi
I tried to understand the link, but to no avail. So I attach a test file.

It has been working fine the way it is - the total of the sheet cell R286 (when unfiltered is the same as cell AC296 and I get the constituent part totals for Labour; Plant etc.
But, I needed to filter column N to remove every invoice after 15th May, but the SumIfs obviously reflect what is on the sheet, not what is displayed. So I would like a SumIf, based on Sub-Total, if possible, please.

 

[NARAYANK991]

Hi ,

Did you check out the links posted by Chihiro ?

The following formula is from that link :

=SUMPRODUCT(SUBTOTAL(109,OFFSET(R$2,ROW(R$2:R$284)-ROW(R$2),,1)),--(X$2:X$284 = X286))

Enter this in AC286 and copy down.

Narayan

 

[philiphales]

Hi
I did, but was not sure and could not get it to work.
I thank him for his time and you too - this works brilliantly, it will save me a lot of time.