Wednesday, March 7, 2012

yogi_Sum Up An Item In A Column To Fulfill Multiple Criteria

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #460          www.energyefficientbuild.com

user sjchow said:
How to sum total amount in 1 column which needs to fullfill 2 criteria in separate column?
Hi,
I have been trying to computer the total annual leave consumed by each employee.
Column C= Employee code (example: jeremy lee = jjlee)
Column D = no of days employee applied for. (Number is either 0.5 or 1 represent half or full day)
Column E= Type of Leave (AL= Annual leave...etc)
For example, how do I calculate the total annual leave taken by Jeremy?
I have tried : "=SUM(SUMIF(C:C='sjchow',{0.5,1},E:E="al"))" but it still prompt parse error.
I have tried filter function as well. It did successfully but I can't sum up the whole column amount.
Thanks!
------------------------------------------
following is a solution to the problem

4 comments:

  1. Hi,
    Thank you so much. This is much easier without having to create a separate record and it auto generates. It works very well.

    ReplyDelete
  2. Hi Sandy:

    I am glad the solution works well for you ... Now Let Us Keep Googling.

    Cheers!
    Yogi

    ReplyDelete
  3. Dear Yogi,

    Would need your help again if it's not too much to ask.
    I am trying to use above function in Sheet A but extracting the data from sheet B.
    When I use the above function in sheet B itself, it works fine. But When i try to use below, it kept prompt me "Parse error"

    =query("sheetB"!A:D,"select "sheetB!A:A",sum("sheetB"!D:D)group by "SheetB"!A:A)

    I have tried the =sumif("sheetB"!A:A,"C0001","sheetB"!D:D)but it's either 'parse error' or it comes with wrong figure. Example, C0001 should have "2" count, but it came back as "0" instead.

    Example:
    Sheet B
    Employee Code Date Expiry Date No of Day Reason
    C0001 XX/XX/XXXX XX/XX/XXXX 1
    S0002 XX/XX/XXXX XX/XX/XXXX 0.5
    J0003 XX/XX/XXXX XX/XX/XXXX 1
    J0003 XX/XX/XXXX XX/XX/XXXX 1
    C0001 XX/XX/XXXX XX/XX/XXXX 1

    Thank in advanced!

    ReplyDelete
  4. Hi Sandy:

    instead of using:
    =query("sheetB"!A:D,"select "sheetB!A:A",sum("sheetB"!D:D)group by "SheetB"!A:A)

    use:
    =query(sheetB!A:D,"select A,sum(D)group by A")

    Cheers!
    Yogi

    ReplyDelete