yogi_CountCheckBoxResponsesFromFormSubmittals

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

kasumner said:
I am building a writing rubric form and corresponding spreadsheet. I am having trouble assigning a point value to each checked box to allow the total number of checked boxes for a section to render a total score on the spreadsheet. I have tried to use the "COUNTIF" formula but cannot figure out the correct syntax.
When I have 5 potential boxes for "organization" and a student scores in two areas, the two areas show in cell G2 of the spreadsheet. I would like to add a column next to it and have the program count the number of selections and assign a score. This is the formula I tried in cell H2 but it is not working. =COUNT(IF(ISTEXT(G2:text_A,text_B,text_C,text_D,text_E)))
This is the information in the cell which should have a value of two.
C. 4-6 complete sentences, D. 7 or more complete sentences
---------------------------------------------------------------------------------
In the ResponseTable in Google spreadsheet, the responses from CheckBoxes that are checked (tick marked) get all lumped up together and if 3rd and 4th CheckBoxes were checked in response to a question ... in the ResponseTable those will be shown as items C. and D.

In the following solution, I used the COUNTIF function to sum up the responded to CheckBoxes (out of 5), I am going to count how many of A., B., C., D., and E. are present in an answer. So here we go ...



The formula in cell H2 of sheet yogi_ECAW Raw Scores is:
=sum(countif(G2;{"*A.*","*B.*","*C.*","*D.*","*E.*"}))
this formula is then copied down to cells H3, H4, ...
---------------------------------------------------------------------------------

Kim (kasumner) commented ...
I finished formatting the cells. The new data points entered from the form are not entered onto a row with the formula; it creates a new row. How can I get the new data to enter onto a row with the formulas?


Kim, for that we will need an arrayformula ... so I have added the sheet yogi2_ECAW Raw Scores. I added a blank row 2 and I added the following formula in cell H2 ...

=ArrayFormula(if(len(G2:G),mmult(iferror(sign(find({"A.","B.","C.","D.","E.","F."},G2:G))),sign(transpose(column(A1:F1)))),iferror(1/0)))

I added the formula in cell H2 of a blank row, so the formula will apply even to the first Form submittal  logged in the Response Table.

yogi_MultipleSUMIFsProblem

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

timm.acdd said ...
I am having difficulty trying to get google docs to recognize my sumifs formula. I have read that it doesn't have a sumifs formula and I should just add them instead. This doesn't work for my formula. This is what I basically need:

I need cell to look in column 'Client Table'!$E:$E, check to make sure it is the same as the column that my cell is in (C$3), then check to see if there is a "1" in column 'Client Table'!$H:$H. Once it checks these 2 columns for those criteria, then it should sum column 'Client Table'!$D:$D.

In essence, check the 2 columns and add together everything that the 3rd column has in it.

------------------------------------------------------

Google spreadsheet does not have SUMIFS function ... in the following solution, I have used the SUMPRODUCT function to deal with multiple SUMIFs



I have also added sheets UsingQUERY, and UsingFilter.

yogi_FindWhichColumnHasMaxNumberOfEntries

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

rajvivek said ...
I have a spreadsheet in which the 1st row (A1:AL1) has different headers - say Header1, Header2, Header3 etc. The sheet is long till AL100 in which some cells are empty, and some are non-empty.

I want a formula that could tell me in A101:B101 - 1) the name of the column (for example, Header2) which has the highest no. (instances) of non-empty cells and, 2) Total no. of such instances (non-empty cells) of that particular column.
------------------------------------------------------

for developing this solution, I used a named range yRange for data starting from row2 down. And for this illustration, yRange has data in cells A2:L8

yogi_QueryWithOpenEndedColumnAndRowRanges

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

I am going to address here a case where not only the rows range is open-ended, but also the columns range is open-ended in that one can add more fields (columns) 

because the queried range can expand both horizontally and vertically, I thought it is best to take the SourceData in one Sheet and run the QUERY in tne ResultsSheet

What I am trying to compute here are Day totals (Day1, Day2,Day3, ....) for unique items in column A.

So, here we go ...

If more fields (columns), say Day4, Day5, etc. are added to the SourceDta, the Results Sheet will self adjust and expand to suit.

yogi_QueryFunctionWithColumnAndRowCriteria

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

parefr said ...
I am trying to use the filter function with a condition base on a colum and a condition base on a row. The data look like this:


Row
1 Item Color Day1 Day2 Day3
2 AAA   Red     5    0    3
3 AAA   Blue    1    2    4
4 AAA   Yellow  6    2    1
5 BBB   Black   0    2    3
6 BBB   Red     7    6    2


The results I would like is:


Row
9  Item Day1 Day2 Day3
10 AAA   12     4    8
11 BBB    7     8    5
------------------------------------------------


In the following solution I have used the QUERY function

parefr commented ...
I see what your query is doing, but I will have to change the query every time I add a new day. Is there another way to do it?


Yes we can change the range from A1:E6 to A:E ... I have added Sheet2 to illustrate this -- the formula in cell G1 is ...
=query(A:E,"select A,sum(C),sum(D),sum(E) where A<>'' group by A label sum(C) 'Day1', sum(D) 'Day2', sum(E) 'Day3' ")

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

yogi_TotalHoursByDayFromSortedListOfDateAndTime

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Gen 3 Electric said:

I have a form that is giving me date, time and start and stop time. I want to see when the date changes then find the difference from the first time entered from the last time entered for each date. 


Date      Time as   Answer I'm looking for

                 a #

1-1-2011     7.5
1-1-2011     8.5

1-1-2011     11

1-1-2011     16            8.5

1-2-2011     7.5

1-2-2011     12

1-2-2011     16.5          9

1-3-2011     7.5

1-3-2011     11

1-3-2011     13

1-3-2011     14             6.5

--------------------------------------------------
In Sheet1 I used Gen 3 Eletric's source data using the range of values in cells A2:B12 with the following formula in cell C2 ...
=ArrayFormula(if(A2=A3,"",max(if(A$2:A$12=A2,B$2:B$12))-min(if(A$2:A$12=A2,B$2:B$12))))

--------------------------------------------------
Gen 3 Electric commented ...
I'm not sure where I'm where I'm going wrong. I can see how it can work. Thanks for the direction. I tried upping range from A12 to A51. That is because I have a few hundred entries. Could I use A:A?


I added Sheet2 to use open ended ranges A2:A, and B2 using the following formula in cell C2 ...
=ArrayFormula(if(A2=A3,"",max(if(A$2:A=A2,B$2:B))-min(if(A$2:A=A2,B$2:B))))