Sum based on various column data

kelsey.beckmeyer

Hi all,

I am trying to build a formula to compare investment amounts of different program types that serve different populations to compare the investment amounts across populations:

For example, I want to get a sum of the Funding Amount for Program Type "Diversion" serving Sub-Pop "Families" and then be able to create a chart to compare that to Program Type "Diversion" serving Sub-Pop "Single Adults"

Any suggestions?!

THANK YOU!

Devin Lee

Create another sheet and reference the existing sheet, or use the Sheet Summary feature. Really depends on how many comparisons you are making. Personally, I like to use the Sheet Summary if you are only comparing one at a time and a new sheet if you intend to have multiple caparisons.

For Sheet Summary

=SUMIFS([Funding Amount]:[Funding Amount], [Program Type]:[Program Type], [Program Type 1]#, [Sub-Pop]:[Sub-Pop], [Sub-Pop 1]#)

For a Sheet that is referencing the existing sheet

=SUMIFS({Ref 1:[Funding Amount]:[Funding Amount]}, {Ref 2:[Program Type]:[Program Type]}, [Program Type]@row, {Ref 3:[Sub-Pop]:[Sub-Pop]}, [Sub-Pop]@row)

After you have the numbers you can create a dashboard that pulls in the data you want.

Devin Lee

Create another sheet and reference the existing sheet, or use the Sheet Summary feature. Really depends on how many comparisons you are making. Personally, I like to use the Sheet Summary if you are only comparing one at a time and a new sheet if you intend to have multiple caparisons.

For Sheet Summary

=SUMIFS([Funding Amount]:[Funding Amount], [Program Type]:[Program Type], [Program Type 1]#, [Sub-Pop]:[Sub-Pop], [Sub-Pop 1]#)

For a Sheet that is referencing the existing sheet

=SUMIFS({Ref 1:[Funding Amount]:[Funding Amount]}, {Ref 2:[Program Type]:[Program Type]}, [Program Type]@row, {Ref 3:[Sub-Pop]:[Sub-Pop]}, [Sub-Pop]@row)

After you have the numbers you can create a dashboard that pulls in the data you want.

kelsey.beckmeyer

Thank you!!!