Sum portion of row based on multiple column header criteria

Bluefish74274

New Member
Joined
Dec 30, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi folks! I'm new to the forum so please forgive any newbie faux pas! I've been scouring the web this afternoon (as well as this forum, which I love!) for a solution to my problem. I have monthly metrics (sales, margin, etc.,) for actuals, budget and last year for various fruit. On another tab in my excel spreadsheet, I'd like to be able to aggregate each metric to quarterly or FY totals. So for example, I'd like Q4 Actual sales or Q4 sales margin for last year. Unfortunately sumproduct is not an option as my raw data is 1K columns wide. Also, I realize that I could append subtotals to the raw data which would significantly widen the data and would be very labor intensive. Was hoping this could be done with sumif +index + match, however I tried using '&' to concatenate the multiple match column criteria (Sales + Actuals + Q4), but as you probably already know, the array formula wouldn't work with sumif. I'm hoping there is a solution that doesn't require VBA code, so please bring it on!! Please let me know if you need anything further.
 

Attachments

  • Capture.JPG
    Capture.JPG
    131.2 KB · Views: 14

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Bluefish74274

Is the image you posted the place where you want these formulas to be or is it the raw data? If it is not the raw data, could you post a sample of that.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
I was wondering the same thing, looking at the screen capture I don't see how that could end up with 1k+ columns of data for just current and previous years but reading the question in detail infers that it is raw data.

@Bluefish74274 does this help with what you're trying to do? (note that this uses random values, not the figures from your example).
gtd562 CountIF.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
5SalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesMarginMarginMarginMarginMarginMarginMarginMarginMarginMarginMarginMargin
6Q3Q3Q3Q4Q4Q4Q3Q3Q3Q4Q4Q4Q3Q3Q3Q4Q4Q4Q3Q3Q3Q4Q4Q4
7ActualsActualsActualsActualsActualsActualsLYLYLYLYLYLYActualsActualsActualsActualsActualsActualsLYLYLYLYLYLY
8FruitJulAugSepOctNovDecJulAugSepOctNovDecJulAugSepOctNovDecJulAugSepOctNovDec
9Apples2.8018.8114.114.8417.843.183.344.230.993.804.734.663.5816.228.139.0014.9615.0612.308.188.365.5010.5316.95
10Oranges7.0211.539.404.957.229.9218.5518.3013.159.4812.4517.5717.2817.2216.6311.867.2815.622.7110.2219.623.5613.9416.49
11Pears2.441.9913.883.6619.234.1910.622.5818.3410.3418.990.927.8511.6012.3319.7312.254.295.866.294.3210.8915.0517.48
12Peaches16.6911.929.537.4217.7317.6511.655.809.779.068.1019.792.089.2316.6712.580.889.231.8317.6212.782.625.4715.97
13Grapes6.4413.6413.462.789.9716.346.593.8216.162.410.9612.7717.6210.1917.7712.221.303.9516.618.2713.0613.0817.147.11
14Strawberries5.769.7710.072.4319.592.7215.6812.3317.686.599.5617.859.913.9019.382.007.918.252.6117.2512.7912.115.367.05
15
16ActualsLYActualsLY
17SalesSalesMarginMargin
18Q10000
19Q20000
20Q3179.26189.58217.59180.68
21Q4171.66170.03168.37196.3
22Year350.92359.61385.96376.98
Sheet4
Cell Formulas
RangeFormula
B18:E21B18=SUM(FILTER($B$9:$Y$14,($B$5:$Y$5=B$17)*($B$6:$Y$6=$A18)*($B$7:$Y$7=B$16),0))
B22:E22B22=SUM(B18:B21)
 

Bluefish74274

New Member
Joined
Dec 30, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Yes the data I posted is a small sampling of the raw data. Numerous other metrics are contained in the raw data spreadsheet. Also it was implied but I should have specified that the aggregate totals on different tabs that I am looking for, would be for each fruit, not total of all fruit.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That should be as simple as reducing the first range of FILTER to a single row and making it relative, although it will most likely need some other minor adjustments to make things work smoothly.

A quick example of how you want the results laid out would give us more to work with.
 

Bluefish74274

New Member
Joined
Dec 30, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
For simplicity, the format in the attached image will be adequate.
 

Attachments

  • Capture.JPG2.JPG
    Capture.JPG2.JPG
    48 KB · Views: 4

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Something like this (using same raw data as my earlier post)?
gtd562 CountIF (version 1).xlsb
ABCDEFGHIJKLM
16SalesSalesSalesMarginMarginMarginSalesSalesSalesMarginMarginMargin
17Q3Q4FYQ3Q4FYQ3Q4FYQ3Q4FY
18FruitActualsActualsActualsActualsActualsActualsLYLYLYLYLYLY
19Apples35.7225.8661.5827.9339.0266.958.5613.1921.7528.8432.9861.82
20Oranges27.9522.0950.0451.1334.7685.8950.0039.5089.5032.5533.9966.54
21Pears18.3127.0845.3931.7836.2768.0531.5430.2561.7916.4743.4259.89
22Peaches38.1442.8080.9427.9822.6950.6727.2236.9564.1732.2324.0656.29
23Grapes33.5429.0962.6345.5817.4763.0526.5716.1442.7137.9437.3375.27
24Strawberries25.6024.7450.3433.1918.1651.3545.6934.0079.6932.6524.5257.17
Sheet4
Cell Formulas
RangeFormula
B19:C24,E19:F24,H19:I24,K19:L24B19=SUM(XLOOKUP($A19,$A$9:$A$14,FILTER($B$9:$Y$14,($B$5:$Y$5=B$16)*($B$6:$Y$6=B$17)*($B$7:$Y$7=B$18),0)))
D19:D24,G19:G24,J19:J24,M19:M24D19=SUM(B19:C19)
 

Bluefish74274

New Member
Joined
Dec 30, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thanks @jasonb75. This formula would work great were if it were not for the fact that I would have to specify the look up range for every metric. Given the fact that the raw data has 1K columns, this would be a heavy lift. The aggregate format I sent was just a sample of metrics that I am looking to summarize. Additional examples include: Promotional sales, non-promotional margin. In addition, not just looking at actuals and last year, also looking for aggregates on budget and forecast. So my goal is to find a more automated formula that won't rely entering each lookup range designation. Hope that makes sense.
 

Bluefish74274

New Member
Joined
Dec 30, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I tried out your solution and unfortunately my version of excel doesn't recognize xlookup and filter functions :(
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
If you don't have those functions then I'm guessing that you don't have office 365 as shown on your profile, if you do then it is possibly behind on updates.

I don't think that you're going to be able to automate anything to the point of not setting out the column headings (rows 16 to 18 in the example above), once that is done it is simply a case of entering the first formula (ctrl enter into 2 columns), use Alt + = in the third column for the basic sum then using autofill to populate the rest of the table from those 3 cells.

That said, without the xlookup and filter functions the formula would need to be rewritten using other functions but the setup would be much the same.

The layout of the raw data is not going to work with a pivot table, which only leaves vba or possibly power query, although to be honest I wouldn't know where to start with that.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,717
Members
415,922
Latest member
gemmatay88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top