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
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
Hi,
ReplyDeleteThank you so much. This is much easier without having to create a separate record and it auto generates. It works very well.
Hi Sandy:
ReplyDeleteI am glad the solution works well for you ... Now Let Us Keep Googling.
Cheers!
Yogi
Dear Yogi,
ReplyDeleteWould 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!
Hi Sandy:
ReplyDeleteinstead 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