# Max Sum Condition Formula

#### dfulm219

##### New Member
I have a range of cells that have numbers in them. When the range reaches a specific number, I need this formula to yield a specific value. When it reaches another maximum, I need it to yield a different specific formula.

For Example:

A1=8, A2=8, A3=8, A4=8, A5=8, A6=8, A7=8, etc.

I need a formula that serves the function of:

When the above cells reach 40, read "A" until it reaches 80 then read "B" until it reaches 120 then read "C" until it reaches 160, then read "D".

Does this make sense?

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Haseeb Avarakkan

##### Well-known Member
Hi,

If the difference between values are 40, try this in B1

If you're using Excel 2003 or earlier, this will give you untill IV
If you're using Excel 2007 or later, this will give you untill XFD

#### dfulm219

##### New Member

Ok, let's use this example:

A2=<Cell Value>, B2=<Cell Value, C2=<Cell Value, D2=<Cell Value> ETC until G2

When the above cells reach <Specific Value>, read "A" until it reaches <Cell Value * 2> then read "B" until it reaches <Cell Value * 3> then read "C" until it reaches <Cell Value * 4>, then read "D" until it reaches <Cell Value * 5> then read "E".

Can you help?

#### Haseeb Avarakkan

##### Well-known Member

Ok, let's use this example:

A2=<Cell Value>, B2=<Cell Value, C2=<Cell Value, D2=<Cell Value> ETC until G2

When the above cells reach <Specific Value>, read "A" until it reaches <Cell Value * 2> then read "B" until it reaches <Cell Value * 3> then read "C" until it reaches <Cell Value * 4>, then read "D" until it reaches <Cell Value * 5> then read "E".

Can you help?
What is in A2, B2..... until G2? Below is the sample. It will give you If the SUM

01-39 "A"
40-79 "B"
.......
Above 160 will show "E". If this is not you are looking please provide some sample data.

#### dfulm219

##### New Member

Haseeb -

Here is my example:

D11 through J11 will all be a number. Let's use the number 8 for this example.

D11=8, E11=8 ... J11=8.

I need a formula in D12 that will yield "A" when those numbers (D11:J11) add up to a specific value, we'll use 40 in this example. I need the same formula to yield "B" when the same range of numbers add up to 2x the specific value, 80 in this example. When the same range add up to 3x the specific value, 120, the formula will yield "C" and "D" for 4x the specific number, 160.

To recap, the formula I need will yield the following:

0-40: "A"
41-80: "B"
81-120: "C"
121-160: "D"

This will be based on a specific range of numbers that will repeat infinitely.

In other words, it will read the range - D11:J11 - Over and over again.

I've been racking my head with this for days now. If you could help, I would GREATLY appreciate it!

#### Jonmo1

##### MrExcel MVP
Try

=LOOKUP(SUM(D11:J11),{0,41,81,121,161},{"A","B","C","D",""})

#### jasonb75

##### Well-known Member

=CHAR(65+(SUM(D11:J11)/40))

Works with totals from 0 to 1039 inclusive.

#### dfulm219

##### New Member
Okay, great, that definitely works. I left out a part of the functionality that I need from this formula.

Let me try this again. So the variables of the page are as follows:

• Crew Size
• # of Shifts
• Shift Length
• Working Days
• Remaining Duration of Project
• Maximum Weekly Hours
All other cells are calculation formulas.

The formula I need would go in cell D12 and continue down to J15.

In each of these cells, I need the formula to yield "A" until the "Maximum Weekly Hours" is met (J3+1), then start yielding "B" until the "Maximum Weekly Hours" is met again (J3*2+1), then yield "C" until it is met again(J3*3+1), then yield "D" (J3*4+1)

The letters represent specific crews. Another ideal function of the formula would be if it could recognize "# of Shifts" In this example, the formula would never go past "B" because there are only two crews plugged in the "# of Shifts" cell. If E2 read 4, it would go up to "D" and so forth.

I hope this makes sense and I hope my image shows up.

#### Jonmo1

##### MrExcel MVP
Perhaps

=LOOKUP(SUM(D11:J11),J3*{0,1,2,3}+1,{"A","B","C","D"})

#### jasonb75

##### Well-known Member
While I'm trying to make sense of that, is it possible that the formula could go past "Z"? For example, (J3*27+1)

I have an alternative formula that could cope with that, and way beyond if it's needed.

edit: if I'm reading it right, maybe

=CHAR(65+(SUM(D11:J11)/(J3+1)))

Last edited: Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,381
Messages
5,836,922
Members
430,463
Latest member
mikmob ### 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