Amount based on Shortage or Payment or Adjustment

[suhabthan]

Dear Genious,
here i have attached an excel sheet for receptionist SHORTAGE AND PAYMENT AND ADJUSTMENT, According to the day to day shortage /payment /adj, that i need a summery report below,
can any 1 give an idea to solve it ?

Ex. if i select any employee and their shortage / pymnt/adj, based on that formula should give the results.


rGRDS.

[Pete_UK]

You can simplify the formula you have in columns F, G and H. Put this in F5:

=SUMIF($J$4:$CX$4,F$4,$J5:$CX5)

then copy into G5:H5, and then copy F5:H5 down to row 18.

Isn't this what you want in your summary table?

Hope this helps.

Pete

[suhabthan]

@Pete_UK
Brother its not workng properly bz i need the amount from the individual days, not from summery and that the catergory can be changable
ex. Name "A", catergory can be shortage / paymnt/ adj , when we change the catagory for mentioned employee name that the amount should change.

thnx

[Pete_UK]

But you don't have any dates in your file - just a red-coloured cell J2 with the word date in it. Please explain exactly what you want to do and submit another up-to-date workbook if necessary.

Or, you could look in XL Help for details of the SUMIFS function.

Pete

[suhabthan]

red colourd cell is mentioning about the name of the receptionist and 1,2,3 those are the dates.
Ex.
if i select Employee "J" on date "7"- with Shortage catergory........ that the answer should be 946/-,

if i select Employee "J" on date "7"- with Payment catergory........ that the answer should be 4266/-,

[Pete_UK]

Okay, I understand a bit better now. Put this formula in D24:

=SUMIFS(INDIRECT("J"&MATCH(C$23,B$5:B$14,0)+4&":CX"&MATCH(C$23,B$5:B$14,0)+4),J$3:CX$3,$C24,J$4:CX$4,D$23)

and copy down to D33. Then change the name in C23 and/or the type in D23 to see the values change.

The attached file shows this in operation. If this is what you wanted, please mark the thread as Solved. Also, you can click on the "star" icon in the bottom left corner of any post that has helped you in order to pass on your thanks more directly.

Hope this helps.

Pete

[suhabthan]

Thank U brother its working.

[suhabthan]

=SUM(IF($J$3:$CX$3=C25,IF($J$4:$CX$4=$D$23,IF($B$5:$B$14=$C$23,$J$5:$CX$14,""))))

i have an idea like this also (array formula), this is also workng, thnk u for ur effort.

[Pete_UK]

You're welcome - perhaps you can mark the thread as Solved.

Pete