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 |
2 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.
Can you provide an example of the expected result(s) for the sample data, with an explanation?
