LOOKING FOR A FORMULA

[sudipto3003]

Dear all,

I have a file, where the user will fill the Sheet2, and the Sheet1 will automatically update. The report is monthly division wise expense details. May be one particular expense repeat more than one times for a single division in a particular month. I have attached herewith the example file and looking for your help.

 

[Somendra Misra]

@sudipto3003

Hi, if you demerge your month cell in Sheet1 and put in Apr-13 above A,B,C,D than you can use below formula.

=SUMPRODUCT((C$2=Sheet1!$B$2:$O$2)*(Sheet2!$B3=Sheet1!$B$3:$O$3)*(Sheet1!$A$5:$A$9=Sheet2!$A3)*(Sheet1!$B$5:$O$9))

Advise if any isuue.

Regards!

 

[Hui]

Try in Sheet1!B5:
=SUMPRODUCT((Sheet2!$C$3:$H$9),(Sheet2!$A$3:$A$9=$A5)*(Sheet2!$B$3:$B$9=B$3)*(Sheet2!$C$2:$H$2=$B$2))
Copy across & down to E9

You will have to manually adjust the reference to B2 in each block/Month

Refer: http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[sudipto3003]

thanks to both somendra & hui for your quick response, I have found what I need.

 

[Somendra Misra]

@sudipto3003

Glad you got it.

Regards!