SUMIF with OFFSET

[RBusiness]

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

 

[63falcondude]

How about something like this?

Excel 2010
	A	B	C
1	82
2	54321
3	54321	44444	54321
4	50	1000	7
5	54321	44444	44444
6	25	1000	1000
Sheet1

Cell Formulas
Range	Formula
A1	=SUMIF(A3:C5,A2,A4:C6)

 

[RBusiness]

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?

 

[63falcondude]

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.

 

[RBusiness]

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

 

[63falcondude]

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

Great! Glad to help.

 

[XL Grasshopper]

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!