Google Spreadsheet Post #2057
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Apr-07-2016
comment by: David Brinkley:
in my following blog post:
yogi_Computation Involving Multiple And Or Conditions
I have a similar question so I hope you are still monitoring this old post.
I am making a log for my son to use to record his daily activity in his online school
Here is the situation...
the data looks like this
Date | Subject | Time IN | Time Out | Time Total |
04/07/2016 | English | 7:00 AM | 8:00 AM | 1:00 |
04/07/2016 | Math | 8:00 AM | 9:00 AM | 1:00 |
04/07/2016 | Science | 10:00 AM | 11:00 AM | 1:00 |
04/07/2016 | Math | 11:00 AM | 12:00 A M | 1:00 |
04/07/2016 | Economics | 12:00 AM | 1:00 AM | 1:00 |
04/07/2016 | Spanish | 1:00 AM | 2:00 AM | 1:00 |
04/07/2016 | History | 2:00 AM | 3:00 AM | 1:00 |
04/07/2016 | English | 3:00 AM | 4:00 AM | 1:00 |
04/07/2016 | History | 4:00 AM | 5:00 AM | 1:00 |
As you can see, he works on some subjects several times a day. I need a function that will check lines Sheet2!A2:A1001 to see if any of them match the date for the current line (Sheet1!A2) and if the date matches then it needs to compare the "Subject" to see if it matches, lets say, "Math" and if the date and subject both match then it will add the Total time to a running total for that subject on that date.
So it looks at
Line 1, date match, subject no match
Line 2 date match, subject match so +1 Math=1
Line 3 date match, subject no match
Line 4 date match, subject match so +1 Math=2
Line 5 date match, subject no match
Line 6 date match, subject no match
Line 7 date match, subject no match
Line 8 date match, subject no match
Line 9 date match, subject no match
So
Date | Math | Science | English | Spanish | Economics | History |
4-7-2016 | 2 | 2 | 1 | 1 | 1 | 2 |
Like so. So anywhere on sheet 2 column A that the date 4-7-2016 appears it needs to then check the subject and add it to the correct column for that date on sheet 1.
Rather then writing the formula for me I would love to just understand the syntax. Don't get me wrong lol you can write it for me but also explain it to me.
I am making a log for my son to use to record his daily activity in his online school
Here is the situation...
the data looks like this
Date | Subject | Time IN | Time Out | Time Total |
04/07/2016 | English | 7:00 AM | 8:00 AM | 1:00 |
04/07/2016 | Math | 8:00 AM | 9:00 AM | 1:00 |
04/07/2016 | Science | 10:00 AM | 11:00 AM | 1:00 |
04/07/2016 | Math | 11:00 AM | 12:00 A M | 1:00 |
04/07/2016 | Economics | 12:00 AM | 1:00 AM | 1:00 |
04/07/2016 | Spanish | 1:00 AM | 2:00 AM | 1:00 |
04/07/2016 | History | 2:00 AM | 3:00 AM | 1:00 |
04/07/2016 | English | 3:00 AM | 4:00 AM | 1:00 |
04/07/2016 | History | 4:00 AM | 5:00 AM | 1:00 |
As you can see, he works on some subjects several times a day. I need a function that will check lines Sheet2!A2:A1001 to see if any of them match the date for the current line (Sheet1!A2) and if the date matches then it needs to compare the "Subject" to see if it matches, lets say, "Math" and if the date and subject both match then it will add the Total time to a running total for that subject on that date.
So it looks at
Line 1, date match, subject no match
Line 2 date match, subject match so +1 Math=1
Line 3 date match, subject no match
Line 4 date match, subject match so +1 Math=2
Line 5 date match, subject no match
Line 6 date match, subject no match
Line 7 date match, subject no match
Line 8 date match, subject no match
Line 9 date match, subject no match
So
Date | Math | Science | English | Spanish | Economics | History |
4-7-2016 | 2 | 2 | 1 | 1 | 1 | 2 |
Like so. So anywhere on sheet 2 column A that the date 4-7-2016 appears it needs to then check the subject and add it to the correct column for that date on sheet 1.
Rather then writing the formula for me I would love to just understand the syntax. Don't get me wrong lol you can write it for me but also explain it to me.
No comments:
Post a Comment