Function to Sum a number of rows based on a variable

Hello!

I've been searching for an answer to my question, but unfortunately I'm not sure exactly how to describe my problem so I have been unsuccessful with my search. If anyone could help me with this I would be very appreciative.

As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.

The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.

It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.

If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.

Greatly appreciate any help! Thanks.

Hi and welcome to the forum. Unfortunately 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.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your 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)

Im not sure exactly which ranges you are looking at, but this is the syntax/function you would use...
=SUMIF($J$14:$J$113,"<="&L3,$I$14:$I$113)

adjust the ranges to what you need

Originally Posted by FDibbins

Im not sure exactly which ranges you are looking at, but this is the syntax/function you would use...
=SUMIF($J$14:$J$113,"<="&L3,$I$14:$I$113)

adjust the ranges to what you need

thanks for the reply. But im not sure SUMIF is the function I am looking for. I don't think it is a sum "based on a criteria".

I wish I knew excel better so I could clearly express my problem.

Lets say I have 10 rows, with the values: 1,1,1,1,1,1,1,1,1,1

I have a random number generated which = 5. So lets say x=5.

How do I add up the first x rows? In this case 5. (1+1+1+1+1)

The way I structured that formula was to sum all data that had week's <= (in this case) 5

Im not sure which columns you are refering to, and what a sample answer woud look like, so if you could provide that, it would help

Maybe something like this...

Data Range

	A	B	C	D
1	Data	-----	Number of cells	Sum
2	1		5	5
3	1
4	1
5	1
6	1
7	1
8	1
9	1
10	1
11	1

This formula entered in D2:

=SUM(A2:INDEX(A2:A11,C2))

If C2 is an empty cell the formula will sum the entire range.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.