Fetch the data from other sheet and sum the same month

[fun2bala]

Hi,
we have a salary format in which following details are there:
A2-Month
B2 -01/04/17
B18-net payment (b18 may change monthly upwards and downwards)
b12- advances (b12 may change monthly upwards and downwards)
and other things is sometimes monthy there will be two formats in same month and amount column is "e"
i tried this formula it works but when there is two format in same month then it doesnt takes the other month format
FORMULA I USED:
IFERROR(SUMIFS(INDEX(INDIRECT("'"&$A10&"'!A:CZ"),,MATCH(C$8,INDIRECT("'"&$A10&"'!2:2"),0)+3),INDEX(INDIRECT("'"&$A10&"'!A:CZ"),,MATCH(C$8,INDIRECT("'"&$A10&"'!2:2"),0)),$B10),"")

 

[vletm]

fun2bala
1) Have You checked manually those row 2's 'wrong' results?
eg XYZ has three 'tables' for 'Apr-17' and so on ...

2) I solved this 'my-way' ... without those ... formulas.
You could see results from rows 22&23 and
You would compare those to Your results.
Select needed (advanced/NET PAYMENT) from dropdowns B2&B3.

 

[fun2bala]

yes its my mistake amount has changed. but when i use drop down the figure has to change.

 

[vletm]

fun2bala
What The figure ..?
Did those 22&23 rows results change then You used dropdowns?
If not then, do You have macros allowed?

 

[bosco_yip]

Maybe...........

Formula solution

1] To select "Net Payment" or "advance" by click B2 &B3 dropdown list

2] In C2, formula copied across and down :

=IFERROR(SUMIF(INDEX(INDIRECT("'"&$A2&"'!2:2"),MATCH(C$1,INDIRECT("'"&$A2&"'!2:2"),0)):INDEX(INDIRECT("'"&$A2&"'!30:30"),INDEX(MATCH(2,1/(C$1=INDIRECT("'"&$A2&"'!2:2"))),0)),$B2,INDEX(INDIRECT("'"&$A2&"'!2:2"),MATCH(C$1,INDIRECT("'"&$A2&"'!2:2"),0)+IF($B2="advance",2,3))),"")

Regards
Bosco

 

[fun2bala]

Hi Mr. Bosco,

its great working and thanks for quick reply.

we need some correction that i dont want drop down box.
if we need that change we can put it manually rather than drop down box.

thanks once again i was working it for past a couple of days.

And if possible can u explain it (formula).

 

[bosco_yip]

Hi Mr. Bosco,

its great working and thanks for quick reply.

we need some correction that i dont want drop down box.
if we need that change we can put it manually rather than drop down box.

thanks once again i was working it for past a couple of days.

And if possible can u explain it (formula).

Explanation of formula :

=IFERROR(SUMIF(INDEX(INDIRECT("'"&$A2&"'!2:2"),MATCH(C$1,INDIRECT("'"&$A2&"'!2:2"),0)):INDEX(INDIRECT("'"&$A2&"'!30:30"),INDEX(MATCH(2,1/(C$1=INDIRECT("'"&$A2&"'!2:2"))),0)),$B2,INDEX(INDIRECT("'"&$A2&"'!2:2"),MATCH(C$1,INDIRECT("'"&$A2&"'!2:2"),0)+IF($B2="advance",2,3))),"")

Breakdown of the formula structure as in :

=IFERROR(SUMIF(RangeStart:RangeEnd,Criteria,SumRangeStart),"")

Whereas :

RangeStart : =INDEX(INDIRECT("'"&$A2&"'!2:2"),MATCH(C$1,INDIRECT("'"&$A2&"'!2:2"),0))

RangeEnd : =INDEX(INDIRECT("'"&$A2&"'!30:30"),INDEX(MATCH(2,1/(C$1=INDIRECT("'"&$A2&"'!2:2"))),0))

Criteria : =$B2

SumRangeStart : =INDEX(INDIRECT("'"&$A2&"'!2:2"),MATCH(C$1,INDIRECT("'"&$A2&"'!2:2"),0)+IF($B2="advance",2,3))

Regards
Bosco

 

[fun2bala]

Nice explanation Mr bosco.
Actually its simple formula of sum and in that we have used index match indirect.
very superb i liked ur way of explaning.


without drop down i cant use this bcoz in place of advance there may be pf esic pt etc.
whereas in if condition IF($B2="advance",2,3)) in place of advance pf, esic pt etc how we can mention it