Formula to copy down and across to size of range in previous column

Peter Davison

Active Member
Joined
Jun 4, 2020
Messages
433
Office Version
  1. 365
Platform
  1. Windows
I have a column (say A) with 100 rows of product numbers
I have a row (say 1) with 50 columns (C onwards) of store numbers
I have a sumifs in the body to calculate sales by product and by row
Is it possible to create a sumifs cell formula that spills to the last row and last column without me having to copy it down?
The reason is that my 100 rows of product numbers and 50 columns of store numbers can be variable.
Any help on this would be great.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
My guess:
MrExcelPlayground4.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1ColumnRowsDataData123456789101112131415161718
2A110100A100010101020103010401050106010701080109011001110112011301140115011601170
3B211101B110011111122113311441155116611771188119912101221123212431254126512761287
4C312102C120012121224123612481260127212841296130813201332134413561368138013921404
5D413103D130013131326133913521365137813911404141714301443145614691482149515081521
6E514104E140014141428144214561470148414981512152615401554156815821596161016241638
7F615105F150015151530154515601575159016051620163516501665168016951710172517401755
8G716106G160016161632164816641680169617121728174417601776179218081824184018561872
9H817107H170017171734175117681785180218191836185318701887190419211938195519721989
10I918108I180018181836185418721890190819261944196219801998201620342052207020882106
11J1019109J190019191938195719761995201420332052207120902109212821472166218522042223
12K1120110K200020202040206020802100212021402160218022002220224022602280230023202340
13L1221111L210021212142216321842205222622472268228923102331235223732394241524362457
14M1322112M220022222244226622882310233223542376239824202442246424862508253025522574
15N1423113N230023232346236923922415243824612484250725302553257625992622264526682691
16O1524114O240024242448247224962520254425682592261626402664268827122736276027842808
17P1625115P250025252550257526002625265026752700272527502775280028252850287529002925
18Q1726116Q260026262652267827042730275627822808283428602886291229382964299030163042
19R1827117R270027272754278128082835286228892916294329702997302430513078310531323159
20S28S280028282856288429122940296829963024305230803108313631643192322032483276
21T29T290029292958298730163045307431033132316131903219324832773306333533643393
22U30U300030303060309031203150318032103240327033003330336033903420345034803510
23V31V310031313162319332243255328633173348337934103441347235033534356535963627
Sheet33
Cell Formulas
RangeFormula
F1:W1F1=TRANSPOSE(B2:B19)
E2:E23E2=A2:A23
F2:W23F2=SUMIFS(C2:C23,A2:A23,E2#)*SUMIFS(D2:D19,B2:B19,F1#)
Dynamic array formulas.
 
Upvote 0
Can you post some sample data & expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Just before I send some data.
This is the formula I am using , it is a Sumifs with two criteria's, however it is looking at a different file (Performance Data.xlsx) to return the result in a different file.
I just added the hash # to the end of this formula and it just returned a zero in the first cell.

=SUMIFS('Performance Data.xlsx'!$C:$C,'Performance Data.xlsx'!$B:$B,"="&$Q6,'Performance Data.xlsx'!$A:$A,"="&S$5)
I added the # before the last bracket.

The first column of products comes from a unique spill from another file
The first row stores also comes from a unique spill using transpose

Any thoughts on how to spill the Sumifs?
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top