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 Sandy:
ReplyDeleteI am glad the solution works well for you ... Now Let Us Keep Googling.
Cheers!
Yogi
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