Thursday, February 6, 2014

yogi_Count Row By Row Number Of Students By Month From A Table Of Months And Student Initials

                                         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"
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 TM1/1/2014 TM1/1/2014 TM1/1/2014 TM1/1/2014 TM1/1/2014 TM1/1/2014 TM
2/2/2014 JM2/2/2014 JM2/2/2014 JM2/2/2014 JM2/2/2014 JM2/2/2014 JM2/2/2014 JM
1/2/2014 JM1/2/2014 JM1/2/2014 JM1/2/2014 JM1/2/2014 JM1/2/2014 JM1/2/2014 JM
1/31/2014 TM1/31/2014 TM1/31/2014 TM1/31/2014 TM1/31/2014 TM1/31/2014 TM1/31/2014 TM
2/5/2014 LM2/5/2014 LM2/5/2014 LM2/5/2014 LM2/5/2014 LM2/5/2014 LM2/5/2014 LM
3/5/2014 YM3/5/2014 YM3/5/2014 YM3/5/2014 YM3/5/2014 YM3/5/2014 YM3/5/2014 YM
2/5/2014 TM2/5/2014 TM2/5/2014 TM2/5/2014 TM2/5/2014 TM2/5/2014 TM2/5/2014 TM

Here is an example of the result

Monthly
JanuaryFebruaryMarch
TM1470
JM770
LM070
YM007

Thank you!
------------------------------------------------------------------------------------------------------------------------------------------------------