•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
Our take
Introducing a dynamic function that efficiently broadcasts sums based on filtered criteria can significantly enhance your spreadsheet capabilities. Given your source table, you need a formula that adapts to changing rows and columns while filtering results based on a specified product list. This scalable solution will allow you to populate your presentation table seamlessly, ensuring that the data aligns perfectly with your requirements. By leveraging this innovative approach, you can simplify data management and focus on achieving your productivity goals.
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Modified PIVOTBY or dynamic array formula to lookup across rows and columnsHi I have the following source data: Week Project Area Target Actual 4-Apr-25 Proj 01 North 50 100 11-Apr-25 Proj 01 North 150 120 18-Apr-25 Proj 01 North 300 50 2-May-25 Proj 01 North 500 70 4-Apr-25 Proj 02 South 10 200 I need a single dynamic formula to spill across rows and columns to give this result : Project Area Measure 4-Apr-25 11-Apr-25 18-Apr-25 2-May-25 Proj 01 North Target 50 100 300 500 Proj 01 North Actual 100 120 50 70 Proj 02 South Target 10 - - - Proj 02 South Actual 200 - - - I tried a PIVOTBY solution but couldn't quite achieve this result, or another option is to go for fixed values for the first three columns & dates and a spilling formula for the values submitted by /u/land_cruizer [link] [comments]
- Dynamic Spill Formula for Lookup To Array With Fixed Week ColumnsHi, I have a source table which is a spill from a GROUPBY result in the following format: Item Area Week Measure 1 Measure 2 Measure 3 PC Area 1 Week 1 50 100 150 PC Area 1 Week 2 100 200 250 PC Area 1 Week 3 150 250 300 PC Area 2 Week 2 20 40 60 PC Area 2 Week 3 80 100 120 I need to present this info in another sheet with weeks in fixed column headers . The desired output is as follows: Item Area Measure Week 1 Week 2 Week 3 PC Area 1 Measure 1 50 100 150 PC Area 1 Measure 2 100 200 250 PC Area 1 Measure 3 150 250 300 PC Area 2 Measure 1 - 20 80 PC Area 2 Measure 2 - 40 100 PC Area 2 Measure 3 - 60 120 For each item-area block, all the measures will be repeated for all weeks. Tried to do a PIVTOBY but hit a block with fixed columns as some weeks might be missing in the data. Currently using a cell-by cell FILTER formula which is very slow, hoping to improve the performance with a single DA formula submitted by /u/land_cruizer [link] [comments]
- Summarise 2D Dynamic Array?Using Excel 365 on Windows 11 This should be simple, but I haven’t manged to find a way to do it (and Copilot has just generated a bunch of #REF! and #CALC! errors…) I’m trying to do some basic forecasting over multiple years, but using dynamic ranges so the start and end dates, and the number of categories forecast can update automatically. I can get to a dynamic forecast by month, but am drawing a blank when I try to summarise that by year. A simplified version of the sheet currently looks like this: https://preview.redd.it/li08zsp8w7rg1.png?width=584&format=png&auto=webp&s=2d61ac0c1d887bb3b18bb3f08a9542fbc72f439b Data entry in B4:E10 Months dynamic range (pink) calculated in C14 =EOMONTH(D2,SEQUENCE(1,F2,0,1)) and spills C14 to AL14, or wherever the last month is. Categories dynamic range (pale blue) calculated in B15 =FILTER(B4:.B10,B4:.B10<>"")) and spills B15 to B20 here. Years dynamic range (peach) is just a helper row =YEAR(C14#). It might not even be necessary other than visually. The Forecast dynamic range (green) is then =XLOOKUP($B15#,$B$4:.$B$10,$C$4:.$C$10,0)*(C14#>=XLOOKUP($B15#,$B$4:.$B$10,$D$4:.$D$10,0))*(C14#<=XLOOKUP($B15#,$B$4:.$B$10,$E$4:.$E$10,0)) All I want to do is put one formula in C25 to calculate the blue dynamic range total by year for each category. The years are a dynamic range (UNIQUE of C14 above) and the categories are just B15#. This it turns out is completely beyond me - I can calculate each row individually using SUMIFS quite easily, but cannot persuade it to calculate one SUMIF for each line using one formula Anyone got a good way to deal with this? Thanks. submitted by /u/sprainedmind [link] [comments]