•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Dynamic function to broadcast sum based on filtered criteria
Hi
I have the following source table:
| Product | Region | Month | Target | Actual |
|---|---|---|---|---|
| Comp | N | Mar-26 | 100 | 50 |
| Comp | N | Jan-26 | 60 | 120 |
| PC | S | Feb-26 | 100 | 20 |
| PC | S | Apr-26 | 10 | 200 |
| Mobile | W | May-26 | 20 | 100 |
I have another table used for presentation with fixed rows and columns arranged this way :
| Region | Col | Jan-26 | Feb-26 | Mar-26 | Apr-26 | May-26 |
|---|---|---|---|---|---|---|
| N | Target | |||||
| S | Actual |
I need a dynamic formula to spill across this grid. The rows and columns can increase so the solution should be scalable. Also there is another range with required list of products. for e.g range A1:A2 with items "Comp" and "PC", so the formula should be able to filter the sum based on items in this list.
So the expected result is :
| Region | Col | Jan-26 | Feb-26 | Mar-26 | Apr-26 | May-26 |
|---|---|---|---|---|---|---|
| N | Target | 60 | 0 | 100 | 0 | 0 |
| S | Actual | 0 | 20 | 0 | 200 | 0 |
[link] [comments]
Want to read more?
Check out the full article on the original site
Tagged with
#rows.com
#Excel alternatives for data analysis
#financial modeling with spreadsheets
#natural language processing for spreadsheets
#AI formula generation techniques
#generative AI for data analysis
#formula generator
#cloud-based spreadsheet applications
#Excel compatibility
#Excel alternatives
#dynamic formula
#broadcast sum
#filtered criteria
#source table
#presentation table
#scalable solution
#required list of products
#fixed rows and columns
#spill across grid
#Target