Subtotal and Sumif combination help

[Sourkraut]

Alright, I need help combining two formulas.

What we need is to subtotal a range based on a criteria and we need that subtotal to change when the range is filtered.

We have two formulas that work seperately but we need to combine them into one.

=SUBTOTAL(9,Summary!H3:H39)

=SUMIF(Summary!F3:F39,B35,Summary!H3:H39)

B35 = The name we're trying reference
Column F is the column that would hold that name
Column H has the values we want to sum

 

[Aladin Akyurek]

Can you help me? I need to combine a sumif and a subtotal in Excel.
like =SUBTOTAL(109;D4:D17) and =Sumif($K$4:$K17;"OK";D4:D17).
Thanks to all!

=sumproduct(subtotal(109;offset(d4;row(d4:d17)-row(d4);0;1));--(k4:k17="ok"))

 

[meytithveasna]

got it,thanks

 

[Aladin Akyurek]

got it,thanks

You are welcome.

 

[meytithveasna]

Aladin could you tell me when we use Vlookup to replace nest if formula &also give some example?
thanks

 

[Aladin Akyurek]

Aladin could you tell me when we use Vlookup to replace nest if formula &also give some example?
thanks

Whenever you want to look up something...

Suppose you want to know the phone number of mister Ford and there is data which consists of records containing names like Ford and the phone numbers associated with these names like:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td]CODE[/td][td]NAME[/td][td]SCORE[/td][td]PHONE[/td][td]ASSOCIATION[/td][/tr]
[tr][td]

2​

[/td][td]dax[/td][td]friedman[/td][td]

23​

[/td][td]23-3456[/td][td]king's college[/td][/tr]
[tr][td]

3​

[/td][td]zax[/td][td]ford[/td][td]

35​

[/td][td]23-2346[/td][td]london school of economics[/td][/tr]
[tr][td]

4​

[/td][td]nax[/td][td]buffett[/td][td]

90​

[/td][td]24-7721[/td][td]koç university[/td][/tr]
[tr][td]

5​

[/td][td]wax[/td][td]jobs[/td][td]

88​

[/td][td]25-4400[/td][td]apple[/td][/tr]
[/table]

Suppose we have Ford in H2, the name we want to look up for his phone number:

In I2 we would enter a look up formula like...

(a)

=VLOOKUP(H2,B:D,3,0)

(b)

=VLOOKUP(H2,B:D,MATCH("phone",INDEX(B:D,1,0),0),0)

(c)

=INDEX(D:D,MATCH(H2,B:B,0))

 

[meytithveasna]

TY,

 

[vnoreau]

Hello, I want a subtotal that will find a criteria in column D and return the sum of values from Column J. So if the criteria is repeated 6 times in column D then return the sum of those 6 rows from column J. Completely at a loss with this one. Can anyone help me please?

 

[steph001234]

Sorry I got the ranges the wrong way round, this should fix it.....

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Summary!H3,ROW(Summary!H3:H39)-ROW(Summary!H3),0)),(Summary!F3:F39=B35)+0)

Hi, thank you for this formula! What is the purpose of the +0 at the end? I can't find anything about it but my application doesn't work without it. Thank you very much!

 

[Sanjeev1976]

Hi, thank you for this formula! What is the purpose of the +0 at the end? I can't find anything about it but my application doesn't work without it. Thank you very much!

Any arithmetic operation in XL coerces text to numbers