# SUMIF with OFFSET

##### Board Regular
Hello everyone,

I'm trying to create an OFFSET with SUMIF but failing. Maybe you can help?

I'm trying to SUM all values that are below "54321"

 A B C D E F G H I 1 Look for the number listed in A2 throughout A3 to R300 and SUM the values below each instance. The result of this cell should be 82 (50 + 25 + 7 =82) Note that the values below 44444 were NOT summed. 2 54321 (this is the value to look for) 3 54321 44444 54321 4 50 1000 7 5 54321 44444 44444 6 25 1000 1000 7 8 9

<tbody>
</tbody>

This is stumping me -got any ideas?
RB

Last edited:

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### 63falcondude

##### Well-known Member

Excel 2010
ABC
182
254321
3543214444454321
45010007
5543214444444444
62510001000
Sheet1
Cell Formulas
RangeFormula
A1=SUMIF(A3:C5,A2,A4:C6)

##### Board Regular
Thanks 63Falcon (nice car),

So this is the same as my original attempt and it works in a small table of data that has nothing else in it (e.g. just like you show in your example). However, when I try it in the bigger document which includes lots of rows of other data with varying types of characters within the cells, it fails and results in a 0. That's why I was thinking offset might be needed.

Thoughts?

Last edited:

#### 63falcondude

##### Well-known Member
Thanks 63Falcon (nice car),

So this is the same as my original attempt and it works in a small table of data that has nothing else in it (e.g. just like you show in your example). However, when I try it in the bigger document which includes lots of rows of other data with varying types of characters within the cells, it fails and results in a 0. That's why I was thinking offset might be needed.

Thoughts?

If your data is from A3 to R300, try the formula =SUMIF(A3:R300,A2,A4:R301).

This formula looks for the value of A2 in the cells A3 to R300 and takes the sum of the cells below each match.

This should work as long as the cells below every 54321 are numbers. If the cells that you are searching for (54321 in this case) show additional text such as "abc54321def", you can adjust the formula to =SUMIF(A3:R300,"*"&A2&"*",A4:R301).

If you can't get this working, try creating a small sample where the formula doesn't work and post it here so we can get it working for you.

##### Board Regular
It actually did work. I made an error when modifying the sum range.
Thank you for your help, it's greatly appreciated.

#### 63falcondude

##### Well-known Member
It actually did work. I made an error when modifying the sum range.
Thank you for your help, it's greatly appreciated.

#### XL Grasshopper

##### New Member
I'm stuck on a similar problem for the past several weeks now.

I have a large file, which consists of customer purchase details since 2016, through 2020. All the data for 2016 is complete, with 2017 half way through, and have set up years 2018 to 2020.

At the end of the file, I have a Summary sheet which, when choosing the month / year, should display the amount we paid as well as the unique reference for that invoice.

I've got to the point where I can retrieve the correct reference ID using OFFSET MATCH MATCH MATCH MATCH.

Applying this formula method to the Amount Paid column only displays the first cell amount paid, whereas I need it to show the full amount paid for that month under any unique reference.

For example, if you were a shop owner, and sold 2 pints of milk on Monday, 5 on Tuesday, 5 on Wednesday, 0 on Thursday and 8 on Friday....the Summary sheet would need to display that 20 pints of milk were sold during that week. With each pint costing \$1, I would need to pay my Milk suppliers \$20, with my reference of GOTMILK201707.

Now, when I got to the Summary sheet, it will display GOTMILK201707, but will only show Mondays 2 pints of milk that I sold, and not display 20 for that week.

I've tried various methods: INDEX MATCH MATCH, OFFSET MATCH MATCH, SUMIF OFFSET INDEX MATCH MATCH, SUMPRODUCT OFFSET MATCH MATCH, SUBTOTAL OFFSET MATCH MATCH....etc, but I either end up with a blank value or 2.

Its driving me nuts and hoping that someone could suggest other methods to my formula or instruct me on where I'm going wrong. I've showed my file to several people with good Excel knowledge, but they are just as baffled as I am.

Here is an example of my Supplier set up sheet:

-- removed inline image ---

This is for the year 2016 transactions:

-- removed inline image ---

And this is the Summary sheet:

-- removed inline image ---

The orange cells above are supposed to sum up all the items in any given month. So for the first entry of \$24 in March should actually be \$40 as in the 2016 worksheet, Mr Trump bought 2 spades on 4th March, and 3 on 11th March. Yet the problem is that the Summary sheet will only show \$24 worth of spades sold as the formula only picks up the first value it comes across with a reference of TRUMPFACE.

Any ideas anyone? Sorry it's a bit long winded!

Replies
3
Views
192
Replies
3
Views
321
Replies
6
Views
140
Replies
2
Views
362
Replies
1
Views
304

1,191,501
Messages
5,986,927
Members
440,067
Latest member
Swatts1

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

### Which adblocker are you using?

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

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