#### 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

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: