CODEWITHSUNDEEP
excelvba
user8829737
user8829737

Reputation:

Change formula to Subtotal

I have this formula

=SUM((Usages!$BR$2:$BR$1065<BX2)+(Usages!$BS$2:$BS$1065>
  BX2)+INT(Usages!$AD$2:$AD$1065)-INT(Usages!$AB$2:$AB$1065)-1)

is there a way to convert it to Subtotal? so that my totals change as i filter data.

Here is a data sample, the formula works to count the frequency in which an hour bin has passed.

Data sample

Upvotes: 1

Views: 137

Answers (2)

user4039065
user4039065

Reputation:

Try,

=SUMPRODUCT((IF(SUBTOTAL(103, OFFSET(BR1, ROW(1:1064), 0)), BR2:BR1065, 1E+99)<A3)+
               (SUBTOTAL(109, OFFSET(BS1, ROW(1:1064), 0))>A3)+
            INT(SUBTOTAL(109, OFFSET(AD1, ROW(1:1064), 0)))-
            INT(SUBTOTAL(109, OFFSET(AB1, ROW(1:1064), 0)))-
           1)

Before hiding rows(9:10):

enter image description here

After hiding rows(9:10):

enter image description here

Upvotes: 1

J.Doe
J.Doe

Reputation: 596

So I had a better look at it and for your first criteria ((Usages!$BR$2:$BR$1065<A3)+(Usages!$BS$2:$BS$1065> A3)), you can use this workaround : =SUMPRODUCT((A2:A32<>G1)*SUBTOTAL(103,OFFSET(A1,ROW(A2:A32)-1,0)))

enter image description here

I am not sure what is the use for you second criteria (+INT(Usages!$AD$2:$AD$1065)-INT(Usages!$AB$2:$AB$1065)-1) as I dont think it returns any valuable result ? At least not in a bottom total cell formula use ?

Upvotes: 0

Related Questions