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
• ## 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

• Author
Posts
WSJon5

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!

Paul T

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.

I’ve attached the LO sheet.

cheers, Paul

• This reply was modified 11 months, 1 week ago by Paul T.
• This reply was modified 11 months, 1 week ago by Paul T.
###### 1 user thanked author for this post.
WSJon5

Hi Paul,

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

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!

Paul T

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

WSJon5

Hi Paul,

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

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.

Paul T

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.
WSJon5

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 10 months, 3 weeks ago by WSJon5.
Paul T

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