SUMIF vertically with an offset

[shaggy101]

Hi all,
I have a sheet with chunks of repeating rows (row 1-6 repeat for column B/C). I am trying to sum all the results of cell C5, C11, C17, for each unique result in cell C3, C9, C15, etc. To accomplish this I tried using SUMIF with an OFFSET sum_range, but i get the #REF! error (G6 result here should be 1.5)
Any suggestions would be greatly appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMIFS(C5:C1000,C3:C998,F6)

 

[shaggy101]

Thanks for reply! That formula gives a result, but the result is 1 when it it should be 1.5?

 

[Fluff]

In that case can you post some sample data.

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.

 

[Joe4]

So, are you wanting to sum up the values in column C if the following conditions are met:
- column B matches the values in F5
- column C matches the values in F6
And then only sum up the values in column C where column B equals "PCC"?

Is that how you are arriving at 1.5?
If not, please explain exactly how you get there, using your example.
(It really is an ugly data structure to work from, making things much harder than they need to be!).

 

[gifariz]

How about (in G6):

=LET(nData,COUNTA(C:C),SUM(FILTER(OFFSET($C$3,0,0,nData),OFFSET($C$1,0,0,nData)=F6)))

*Edit, updated formula

 

[Fluff]

Hadn't noticed that some of the sections are longer than others.
How about

=LET(w,WRAPCOLS(FILTER(C1:C1000,(B1:B1000=F5)+(B1:B1000="Pcc")),2),SUM(IF(TAKE(w,1)=F6,TAKE(w,2))))

 

[shaggy101]

How about (in G6):

=LET(nData,COUNTA(C:C),SUM(FILTER(OFFSET($C$3,0,0,nData),OFFSET($C$1,0,0,nData)=F6)))

*Edit, updated formula

This is it - thank you!

Yes, very ugly data that I sadly cannot change.

 

[Fluff]

This is it - thank you!

Are you sure? In your image if C21 was Jerry Stanford it would add C23 not C24

 

[gifariz]

This is it - thank you!

Yes, very ugly data that I sadly cannot change.

No, I just realized my formula is wrong also. Fluff is right, please try his latest formula