how to sum in multipile conditions

[imran khan]

Projects

201432

201433
weeks
201434

201435

201436

201437
50000 10 69 45 80 45 45
50001 52 85 80 52 80 80
61003 58 78 45 45 45 45
61003 78 50 68 78 68 68
50000 41 20 78 15 78 78
68798 45 15 18 27 18 18



i need a sum formula wich sum from critearia either of projcts or weeks.
lets suppoese if i give 201433, and projct number 5000 it give me answer of 89. if i give 201437 and 61003 it gives me answer 113.

file is attechts for your furter understanding

 

[Somendra Misra]

Hi Imran,

With current sample you can try below formula:

=SUMPRODUCT(F9:K14*(I23=F8:K8)*(I24=E9:E14))

Regards,

 

[Hui]

Try:
=INDEX(F9:K14,MATCH(I24,E9:E14,0),MATCH(I23,F8:K8,0))
or
=SUMPRODUCT((F9:K14)*(F8:K8=I23)*(E9:E14=I24))

 

[ashish mehra]

Hi Hui,

Sumproduct function is fetching the results as required by OP but Index-match is not fetching the same results.

Week: 201433
Project: 50000

Sumproduct result: 89
Index-match result: 69

Please let me know if I am doing some thing wrong. I am attaching a file for your reference.

Regards,
AM

 

[imran khan]

Hi Imran,

With current sample you can try below formula:

=SUMPRODUCT(F9:K14*(I23=F8:K8)*(I24=E9:E14))

Regards,

thanks alot .. misra,

 

[imran khan]

Try:
=INDEX(F9:K14,MATCH(I24,E9:E14,0),MATCH(I23,F8:K8,0))
or
=SUMPRODUCT((F9:K14)*(F8:K8=I23)*(E9:E14=I24))

thanks alot Hui,

 

[Somendra Misra]

Welcome back anytime @imran khan

Regards,