Google Spreadsheet Post #1518
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Feb-06-2014
post by topgun007 (https://productforums.google.com/forum/#!msg/docs/oGszGQYsZnE/KRvY1LdHM0QJ)
Count the number of times for the month"January" with the person initial "TM"
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Feb-06-2014
post by topgun007 (https://productforums.google.com/forum/#!msg/docs/oGszGQYsZnE/KRvY1LdHM0QJ)
Count the number of times for the month"January" with the person initial "TM"
I have a spreadsheet where data is by date and initial name on each cell. Example, 1/11/2014 TM. The range of data goes from H:DD. Question, I am trying to found a formula to do a count by month and by the name initial. Example, count number of times for the month of January with the person initial of TM only. The date can be duplicate with each person and it needs to count them as well.
I was able to found a way to search each month by the first number using this formula, =QUERY(COUNTIF(ARRAYFORMULA( LEFT('Student Signup'!H:DD)),"1")) and able to found a way to search by the initial using this formula, =QUERY(COUNTIF('Student Signup'!H:DD,"*JM")). Having trouble bring the two formula together. The data is running on the older version of google docs which countifs does not work.
Here is an example of the table.
1/1/2014 TM | 1/1/2014 TM | 1/1/2014 TM | 1/1/2014 TM | 1/1/2014 TM | 1/1/2014 TM | 1/1/2014 TM |
2/2/2014 JM | 2/2/2014 JM | 2/2/2014 JM | 2/2/2014 JM | 2/2/2014 JM | 2/2/2014 JM | 2/2/2014 JM |
1/2/2014 JM | 1/2/2014 JM | 1/2/2014 JM | 1/2/2014 JM | 1/2/2014 JM | 1/2/2014 JM | 1/2/2014 JM |
1/31/2014 TM | 1/31/2014 TM | 1/31/2014 TM | 1/31/2014 TM | 1/31/2014 TM | 1/31/2014 TM | 1/31/2014 TM |
2/5/2014 LM | 2/5/2014 LM | 2/5/2014 LM | 2/5/2014 LM | 2/5/2014 LM | 2/5/2014 LM | 2/5/2014 LM |
3/5/2014 YM | 3/5/2014 YM | 3/5/2014 YM | 3/5/2014 YM | 3/5/2014 YM | 3/5/2014 YM | 3/5/2014 YM |
2/5/2014 TM | 2/5/2014 TM | 2/5/2014 TM | 2/5/2014 TM | 2/5/2014 TM | 2/5/2014 TM | 2/5/2014 TM |
Here is an example of the result
Monthly | |||
January | February | March | |
TM | 14 | 7 | 0 |
JM | 7 | 7 | 0 |
LM | 0 | 7 | 0 |
YM | 0 | 0 | 7 |
Thank you!
------------------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment