Need a Formula for a Google Sheet

Topic

New Reply

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!

Reply | Quote

Replies

[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 5 years, 8 months ago by Paul T.
• This reply was modified 5 years, 8 months ago by Paul T.

1 user thanked author for this post.

WSJon5

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

[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 5 years, 8 months ago by WSJon5.

Reply | Quote

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

Reply | Quote