# Help defining range SUM and OFFSET together

1. ## Help defining range SUM and OFFSET together

Hello ExcelForum members,

I want to formulate a function that describes the following:

The sum of all cells, 4 columns left of all cells with the value 10.

I searched the internet and I found the OFFSET function. But in this case I get stuck at =SUM(OFFSET(.....
I don't know how to define (4 cells to the left of every cell containing the value 10)

Can someone help me?

I look forward to any help!

Dyoerdt

2. ## Re: What function to use in this situation?

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

3. ## Re: Help defining range SUM and OFFSET together

Can you post a SMALL sample file and show us what result you expect?

A SMALL file will have about 20 rows worth of data.

4. ## Re: Help defining range SUM and OFFSET together

Hi Tony,

I am not sure how to post a table so I made a screenshot. I hope this helps.
BTW in this case it is 1 column to the left but in my original file I need 4 columns to the left

Capture.PNG

5. ## Re: Help defining range SUM and OFFSET together

Unless the column with the 10s is dynamic, use SUMIF

=SUMIF(E2:E100,10,A2:A100)

6. ## Re: Help defining range SUM and OFFSET together

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

7. ## Re: Help defining range SUM and OFFSET together

Dear JohnTopley,

Thank you so much I was thinking in the complete wrong direction. But now my problem is solved.

Dyoerdt

8. ## Re: Help defining range SUM and OFFSET together

I was able to view the image using alternative steps.

Here's what I think you want...

Data Range
 A B C D E 1 Days Absent Week Number ------ Week Absenteeism per week 2 1 8 7 0 3 2 8 8 3 4 3 9 9 3 5 4 10 10 15 6 5 10 11 15 7 6 10 12 9 8 7 11 13 10 9 8 11 14 0 10 9 12 15 0 11 10 13 16 0

This formula entered in E2 and copied down:

=SUMIF(B\$2:B\$11,D2,A\$2:A\$11)

There are currently 1 users browsing this thread. (0 members and 1 guests)