May 11, 2024
How do I graphically summarize/aggregate quantities for categories of things?
Hi,
Thanks in advance for your help/guidance!
Context:
I volunteer at a Wildlife Rehab center where we use a simple Google Form to collect information such as:
- Animal type such as raccoon, fox, rabbit, etc. (pull down field)
- Quantity (short answer field) and
- Type of action such as new intake/patient, patient released, patient transferred, etc.
What's Working:
The graphs in Google Forms summary tab show separate graphs for the number/percentage of each animal and another for the number of each action. We have the form linked to Google Spreadsheet so the form responses also go there. At the bottom of this post is a snippet of the columns with some sample data.
Help Needed:
While the current Google Forms summary info is good, we need to report on the above mentioned in "What's Working" PLUS (and most importantly), the number of each type of action taken on each type of animal.
It "feels" like I either need to modify the Forms' summary charts (no idea how) - or - we need something such as an Excel Pivot table in a separate tab or similar is needed but I'm completely new to Google Forms and Spreadsheet. We are completely open to changing/enhancing the Form and/or Spreadsheet... :-)
- Is there is a way to structure the Form so the Forms auto-created summary graphs show what we need - and if so, how would we do this? And/Or how do I modify the auto-created graphs in the summary tab?
- If this needs to be accomplished in the linked Google Spreadsheet, can you please provide steps on how we would do this?
Many thanks!
Scott
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
All Replies
Thank you for the very clear statement of the problem. One thing I don't understand is what you want summed. So, for Raccoon, and Intake, is the answer 2 (rows) or 8 (sum of E)?
May 11, 2024
Great question and apologies for not including this more clearly in the original post... Your question prompted me to think of another important condition that needs to be taken into consideration (a date range)
What we need to see is, both along the way and for each year's period (1 Jan 20xx through 31 Dec 20xx) how many of each action type were taken for each animal type.
Your question provides a great illustration and in this example:
- The graph should show 8 Raccoons were taken in (Action=Intake), 5 Red Fox were Intake, 2 Red Fox were Released, 1 Raccoon was Released, 1 Chipmunk was Transferred
- The additional condition your question prompted me to remember is that we need to see this for the following (it is reported to the state Department of Wildlife Resources):
- Current year to date with boundaries of 1 Jan current year through 31 Dec current year). This would be based on the date entered into the form (column B of the Spreadsheet)
- If possible, I would love to also see the summaries mentioned in bullet #1 above over all time to the current date...
I hope the above isn't too rambling :-)... I appreciate your time lending a hand with this!!
Scott
I think we can do all that you want but it would be much easier for me if you could share a spreadsheet with the data so I don't have to re-enter all of it.
May 12, 2024
The remedy will need to work for all future and real time data so if you can “show me the way” using this test spreadsheet, I should (hopefully 😊) be able to see what you have done and port the solution over to our live instance:
Hope this is Ok for you…
Many thanks!
Scott
Year-to-date (Col B is the same year as today and not after today):
=query({arrayformula(DATEVALUE('Form Responses 1'!B2:B)), 'Form Responses 1'!C2:E}, "select Col3, sum(Col4) where Col1<"&today()&" and Col1>"&date(year(today()),1,1)&" group by Col3 pivot Col2")All time-to-date (Col B is less than today)
=query({arrayformula(DATEVALUE('Form Responses 1'!B2:B)), 'Form Responses 1'!C2:E}, "select Col3, sum(Col4) where Col1<"&today()&" group by Col3 pivot Col2")Last edited May 13, 2024
May 13, 2024
May 13, 2024
I will have a look and let you know by Wed as will be mostly offline for next day…
I REALLY appreciate you on this help! :-)
May 13, 2024
How do we get the summed information for all of the other action categories such as 'Released' and 'Transferred' - and any other additional action categories that we add in the future?
Thanks,
Scott
=query(A1:E, "select D, sum(E) group by D pivot C")in G2 of the Form Responses sheet, but it has no filter on date.
May 14, 2024
Yes... all entries should be included... We just break out entries for current-year-to-date in one tab and then across all time (no date constraints) in the other tab...
The most important feature is to be able to, at any point in time, see the number of each action type taken for each animal type... This is what we need to report to our state wildlife folks...
The date is mainly a reference point... Sometimes we may be so busy that we have to enter something from a day or more ago and sometimes we may enter something for a future date knowing that an animal is coming in/being released/etc...
I noticed that the two test entries I made via the form today did not get registered in the YTD nor allTime tabs... Is there something else needed for new entries to pop into these two tabs or (hopefully) I didn't mess up something... :-)
Thanks!
Scott
May 15, 2024
Apologies for the back the forth...
A few things seem to need additional attention...
Please let me know if you are Ok to continue helping... I understand if I'm monopolizing too much time... :-)
- Before the entry made in bullet #2 below, I did notice the number of "raccoon intakes" increased as expected so I think the change from "< to <=" helped - thanks!
- This morning, I added a new entry via the form to test that an entry with date entered into the form of 1/1/2025 would not show in the current YTD but would show in the allTime tab. What I found is that the only tab updated was the FormResponses tab. I also noticed this caused an error on the query line in the YTD tab.
- I noticed in the FormResponses tab several other populated cells beginning in cell G2 and beyond which should not be there... Are these your working/scratch pad area and will be removed once we are finished?
- I believe if the 3 above are resolved + the queries in the YTD and allTime tabs are updated so that they can capture any combination of "Species Common Name" + "Action" which is entered via the Google Form, then we will have hit a home run... Well, thanks to your patience - you have hit a home run... :-)
- You can find in the attached, a snippet of what is referred to in bullet #4 above...
A million thanks!
Scott
Scott
May 15, 2024
Currently, the YTD and allTime tabs only show the summary quantity for the combination of Intake+Raccoon and Intake+Red Fox.
The “Form Responses 1” tab is populated from entries made via the user in Google Forms (https://docs.google.com/forms/d/e/1FAIpQLSfkgM7nlLWeblCBsW9ohXiwMWVs_f-W1QQrv5tYvN_bQEgy2w/viewform?usp=sf_link).
The Magenta text was just my way to show that the two tabs YTD and allTime need to show a summary of any/all of the other species-actions combinations that end up in columns C (Species Common Name), D (Action) and E (Quantity ) in the “Form Responses 1” tab. Of course the only difference in the YTD and allTime tabs is simply that the summaries in the YTD tab are to be limited only to those entries made in the current year (2024 for this year, 2025 for next year, 2026 for the year after, etc).
Hope this helps….
Happy to have a chat by phone if needed…
Scott
May 15, 2024
I got the Pivot Table working by using the Insert > Pivot Table approach (see attached) so we are good for now...
This can be closed...
Many thanks for your help!
Scott 
