Formula to Sum with Offset Function

xcellrodeo

Board Regular
Joined
Oct 27, 2008
Messages
206
Hi to the brilliant MrExcel Community,
I was wondering if I could get some help with the following issue I am having.
I have a large data set in Excel (Office 365 vs) with ca 27000 rows and around 50 columns.
What I would like to do is use a slicker method of summing up by Year.
Currently I am using loads of SUMIF statements in one cell to sum , by row, all adjacent Columns which have say year "2021" in them.
So for example:
Against each product, I have two columns i.e due date followed by a cost column.
From Columns M to CZ there are loads of products each with 2 columns. They are consistent in that the first is always the due date followed by the cost column which is the adjacent one.
Each product is displayed on a different row
What I want to do is create a SUMIF formula against each product that looks in say Row 3 and Columns M to CZ and find the criteria year i.e "2022" and then returns a sum of all the values in adjacent columns in same row, effectively offset by one column to the right of the year date. However, if the due date column matches the year search criteria but the cost in adjacent column is text then it should ignore this and move on to next.

Hope this makes sense.

Any help would be muchly appreciated.
Thanks
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

xcellrodeo

Board Regular
Joined
Oct 27, 2008
Messages
206
Sorry just to clarify...
Currently I am using loads of SUMIF statements in one cell to sum , by row, all adjacent Columns which have say year "2021" in them.
So for example:
Against each product group (Col.A), I have a range of Products (Col.M-CZ) of which each product has two columns i.e due date followed by a cost column.
They are consistent in that the first is always the due date followed by the cost column which is the adjacent one.
Each product group is displayed on a different row
What I want to do is create a SUMIF formula against each product group that looks in say Row 3 and Columns M to CZ and find the criteria year i.e "2022" and then returns a sum of all the values in adjacent columns in same row, effectively offset by one column to the right of the year date. However, if the due date column matches the year search criteria but the cost in adjacent column is text then it should ignore this and move on to next.

Hope this makes sense.

Any help would be muchly appreciated.
Thanks
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,250
Office Version
  1. 365
Platform
  1. Windows
Any ideas?
I suggest you'd get more help if you posted your layout.

Is this along the right lines?

ABCDEFGHIJKLM
1Product AProduct BProduct CProduct DProduct E
2SumYearResultYearCostYearCostYearCostYearCostYearCost
32021$9122021$1232022$4562021SomeText2023$2,0212021$789
Sheet1
Cell Formulas
RangeFormula
B3B3=SUM(FILTER(E3:M3,(D3:L3=A3)*(MOD(SEQUENCE(,COLUMNS(D33:L33)),2)=1),0))
 

xcellrodeo

Board Regular
Joined
Oct 27, 2008
Messages
206
Thanks StephenCrump, that's great. Yes I see your point about the data table. Note made for next time.
Just so I understand, why did you put 'COLUMNS(D33:L33)' in the formula? was this intentional as this doesnt appear to relate to the data rows refered to above?
If you could clarify this point that would be greatly appreciated, thanks
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,250
Office Version
  1. 365
Platform
  1. Windows
Just so I understand, why did you put 'COLUMNS(D33:L33)' in the formula? was this intentional as this doesnt appear to relate to the data rows refered to above?
Sorry, I meant to type COLUMNS(D3:L3). But the key part is D:L, so the formula still works with the typo 33, although not if I subsequently delete Row 33!

The screenshot below shows why I included this component.

In the first example, I am confident I won't find "Jo" in the Cost columns, hence a simple formula will suffice.
The second illustrates the problem when the search year 2021 also appears as a Cost.
The third shows the fix - I have switched to the simpler ISODD rather than using MOD 2.

ABCDEFGHIJKLM
1Product AProduct BProduct CProduct DProduct E
2SumYearResultYearCostYearCostYearCostYearCostYearCost
3Jo$912Jo$123Sam$456JoSomeTextSue$2,021Jo$789
4
52021$2,9332021$1232022$4562021SomeText2023$2,0212021$789
62021$9122021$1232022$4562021SomeText2023$2,0212021$789
Sheet2
Cell Formulas
RangeFormula
B3,B5B3=SUM(FILTER(E3:M3,D3:L3=A3,0))
B6B6=SUM(FILTER(E6:M6,(D6:L6=A6)*ISODD(SEQUENCE(,COLUMNS(D6:L6))),0))
 

Forum statistics

Threads
1,141,478
Messages
5,706,610
Members
421,460
Latest member
Taamrak

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