News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Need a Formula for a Google Sheet

    Home Forums AskWoody support Microsoft Office by version Office 365 and Click-to-Run Need a Formula for a Google Sheet

    This topic contains 7 replies, has 2 voices, and was last updated by  Paul T 3 weeks, 5 days ago.

    • Author
      Posts
    • #1934488 Reply

      WSJon5
      AskWoody Plus

      Hi all,

      I use a Google Sheet to track my charitable donations. Here’s a redacted copy of the spreadsheet that’s public.

      Column B lists the charity (for example, “Redacted #1”).

      Column C lists a category (for example, “Animals”)

      Column D lists the dollar amount (for example, “$25.00”).

      I just created a tab, “Big Picture,” which I’d like to calculate 2 things:

      1. What percentage of my donations go toward each category?

      2. What percentage of my donations go toward each charity?

      These calculations should be updated automatically whenever I add a new donation to the “Money” tab.

      Can anyone help?

      Thanks so much!

    • #1935089 Reply

      Paul T
      AskWoody MVP

      I managed it in LibreOffice so it should work in GS.

      1. Create a Pivot Table to obtain unique Charity names.
      2. Create a Pivot Table to obtain unique Purpose names.
      3. Use a SUMIF formula to get the totals for each.

      Capture

      I’ve attached the LO sheet.

      cheers, Paul

       

      • This reply was modified 1 month, 1 week ago by  Paul T.
      • This reply was modified 1 month, 1 week ago by  Paul T.
      Attachments:
      1 user thanked author for this post.
    • #1940604 Reply

      WSJon5
      AskWoody Plus

      Hi Paul,

      Thank you so, so much! This is super helpful!

      Might I ask one follow-up?

      Because I often add new charities and new categories, would it be possible for these two lists to be populated and updated *automatically*? That way, everything will be synced and I won’t have to do anything manually.

      Thanks again!

    • #1941246 Reply

      Paul T
      AskWoody MVP

      Refreshing the Pivot Table will add new items, then it’s just a matter of copying the formula to the extra rows.

      I assume you can automate things in GS but I have no idea how.

      cheers, Paul

    • #1950609 Reply

      WSJon5
      AskWoody Plus

      Hi Paul,

      So sorry for the delay, and many thanks for your reply.

      It turns out that pivot tables in Google Sheets are auto-refreshing, so the issue I encountered was because I didn’t “refresh” the pivot table in Excel before converting the spreadsheet to Google Sheets.

      Nonetheless, in trying to copy your spreadsheet to my own, I’m really struggling.

      I think the main issue is that the two pivot tables are on the same sheet as the data itself. Ideally, each pivot table would be on its own sheet.

      To this end, I just split the “Big Picture” sheets into two sheets:

      1. Summary of Charities

      2. Summary of Categories

      The link remains the same, and you can now edit the sheet.

      Can I impose on you to possibly recreate the pivot tables and SUMIF in these two new tabs of my Google Sheet?

      That would help me tremendously, and I think I can take it from here.

      Thank you so much for your consideration.

    • #1957417 Reply

      Paul T
      AskWoody MVP

      I’ve added the pivot tables and formulae to a new sheet Pivot Table 1.

      To create the table I selected the required column, then Data > Pivot table.
      I chose “Sum of Account for each category” and deleted the Values – click on the X.

      The formula is: =SUMIF(Money!B:B,A2,Money!D:D)
      Fill down as required.

      cheers, Paul

      1 user thanked author for this post.
      • #1957487 Reply

        WSJon5
        AskWoody Plus

        Thanks, Paul. This is most helpful, and I appreciate it greatly!

        A few nits to pick:

        1. The Grand Totals cells are empty. Can I just delete those rows?

        2. Can I delete columns B and G? (They seem to be empty.)

        3. Is there a way to sort columns C and H by amount? (Data|Sort sheet by… causes all sorts of problems.)

        4. Any idea why I can’t un-italicize A1 and F1?

        5. Is there a way to hide *all* grid lines? (Clearing everything still leaves a horizontal line underneath the header row and above the “grand totals” row.)

        Thanks again!

        • This reply was modified 3 weeks, 6 days ago by  WSJon5.
    • #1958716 Reply

      Paul T
      AskWoody MVP

      You can only hide the blank column.

      Odd about the italics and underlined totals. Must be a Google thing.

      You can’t sort because the table is auto-updated. I don’t know how to turn off auto-update.

      cheers, Paul

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Need a Formula for a Google Sheet

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.