Forum Discussion
Totalling money in categories and sub-categories across multiple columns
Hi Community
I am trying to wrangle a large income dataset for an organisation in Australia.
I have a large spreadsheet that has multiple text categories whose value is tied to a $ amount. Not all Projects receive every category of funding, so some blanks exist.
The categories are treated as text.
So for this example, I need to be able to get the total Funding $ amount for Research Category 10, across Category 1, 2, and 3.
I have hundreds of rows set up like this:
How do I get the total funding amount of each research category in my table? I have literally hundreds of rows set up like this.
| Project Name | Research Category 1 | $ allocated to Cat 1 | Funding Category 2 | $ allocated to Cat 2 | Funding Category 3 | $ allocated to Cat 3 |
Cat research | 10 | $4000 | 15 | $100 | ||
| Dog research | 20 | $50000 | 22 | $3000 | 10 | $5000 |
| Car research | 25 | $9000 | 30 | $80000 | 22 | $1000 |
| Table research | 30 | $1500 | 10 | $1500000 | ||
| Food research | 10 | $150000 | 20 | $4861 | 15 | $3000 |
| Engineering research | 15 | $6000 | 10 | $5000 | ||
| Flag research | 25 | $5600 | 30 | $8000 | 15 | $800 |
| Dust research | 20 | $99665 | 15 | $130 |
In the attached file the categories are text values (left aligned) and the allocated $ are numbers (right aligned).
=SUM(IF($B$2:$F$9=B12,$C$2:$G$9))This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone works with legacy Excel such as Excel 2013.
4 Replies
In the attached file the categories are text values (left aligned) and the allocated $ are numbers (right aligned).
=SUM(IF($B$2:$F$9=B12,$C$2:$G$9))This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone works with legacy Excel such as Excel 2013.
Thank you so much, this is excellent.
Can you provide an example of the expected result(s) for the sample data, with an explanation?
Totals for all funding categories across all projects:
Funding categories: 10, 15, 22, 25, 30
For the above example:
Category 10: $1664000
Category 15: $10030
Category 22: $154526
Category 25: $14600
Category 30: $81500
