#### Sparcot

##### New Member
Hello.

I have the following table:

Line1: Product | Minutes1 | Minutes2 | KAPPA
Line2: Oranges | Africa | | 2 | 1 | 15
Line3: Oranges | Africa | 3 | 1 | 15
Line4: Oranges | Africa | 2 | 1 | 15
Line5: Oranges | S. America | 3 | 1 | 15
Line6: Oranges | S. America | 5 | 1 | 15
Line7: Oranges | S. America | 5 | 1 | 15
Line8: Bananas | S. America | 2 | 1 | 16
Line9: Bananas | S. America | 3 | 1 | 16
Line10: Bananas | S. America | 2 | 1 | 16
Line11: Bananas | Africa | 3 | 1 | 16
Line12: Bananas | Africa | 5 | 1 | 16
Line13: Bananas | Africa | 5 | 1 | 16

I need in column F to do the following:
To check if the sum of columns \$C and D is not bigger than KAPPA (E column) and if the country (B column) has been changed - then returns 1, otherwise returns 0. The next line to check the same, but already the sum \$C\$2:D3; next line the same but the sum \$C\$2:D4 and so on.
Something like that:
=<e2,if(b2=offset(b2,-1,0),0,1),"")
<e2,if(b2=offset(b2,-1,0),0,1),"")
<e2,if(b2=offset(b2,-1,0),0,1),"")
IF(SUM(\$C\$2:D2) "LESS THAN" E2,IF(B2=OFFSET(B2,-1,0),0,1),"")</e2,if(b2=offset(b2,-1,0),0,1),"")

But i need when it gets to another product, i need the sum to start from the line where it was changed. In this case sum \$C\$8:D8; \$C\$8:D9; \$C\$8:D10 and so on.

Hope the explanation was clear.</e2,if(b2=offset(b2,-1,0),0,1),"")
</e2,if(b2=offset(b2,-1,0),0,1),"")

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### FormR

##### MrExcel MVP

<e2,if(b2=offset(b2,-1,0),0,1),"")
<e2,if(b2=offset(b2,-1,0),0,1),"")
Hope the explanation was clear.

</e2,if(b2=offset(b2,-1,0),0,1),"")
Hi, a quick tip - it helps a lot if you also include your expected results along with your example data and description.

Not sure I got all the logic right, but hopefully this puts you on the right path.

#### Sparcot

##### New Member

Hi, a quick tip - it helps a lot if you also include your expected results along with your example data and description.

Not sure I got all the logic right, but hopefully this puts you on the right path.

Excel 2013/2016
ABCDEF
1 Product Minutes1 Minutes2 KAPPAformula
2 Oranges Africa21151
3 Oranges Africa31150
4 Oranges Africa21150
5 Oranges S. America31151
6 Oranges S. America51151
7 Oranges S. America51151
8 Bananas S. America21160
9 Bananas S. America31160
10 Bananas S. America21160
11 Bananas Africa31161
12 Bananas Africa51161
13 Bananas Africa51161

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
F2=IF(AND(SUMPRODUCT((A\$2:A2=A2)*(C\$2:D2))<e2,b2=b1< font="">),0,1</e2,b2=b1<>)

<tbody>
</tbody>

<tbody>
</tbody>

Hello. It's not actually what I need.

• F2 is 1 (OK), because it respects the conditions I need: B2<>B1 and SUM(\$C\$2:D2)<e2
</e2
• <e2
F3 is 0 (OK), because it respects the conditions I need: B3=B2 and SUM(\$C\$2:D3)<e3
</e3
</e2
• <e2
<e3
F4 is 0 (OK), because it respects the conditions I need: B4=B3 and SUM(\$C\$2:D4)<e4
</e4
</e3
</e2
• <e2
<e3
<e4
F5 is 1 (OK), because it respects the conditions I need: B5<>B4 and SUM(\$C\$2:D5)<e5
</e5
</e4
</e3
</e2
• <e2
<e3
<e4
<e5
F6 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B6=B5 (TRUE) BUT SUM(\$C\$2:D6)<e6 (false)
</e6></e5
</e4
</e3
</e2
• <e2
<e3
<e4
<e5
<e6 (false)
F7 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B7=B6 (TRUE) BUT SUM(\$C\$2:D7)<e7 (false)
</e7></e6></e5
</e4
</e3
</e2
• <e2
<e3
<e4
<e5
<e6 (false)
<e7 (false)
F8 is 0 (nOK - it has to be 1), because it SHOULD respect the conditions I need: B8<>B7 but the sum range should already start in \$C\$8 (SUM(\$C\$8:D8)<e8), <strong="">because we switched to ANOTHER PRODUCT </e8),></e7></e6></e5
</e4
</e3
</e2
• <e2
<e3
<e4
<e5
<e6 (false)
<e7 (false)
<e8), <strong=""></e8),></e7></e6></e5
</e4
</e3
</e2
F9 is 0 (OK), because it respects the conditions I need: B9=B8 and SUM(\$C\$8:D9)<e9
</e9
• <e9
F10 is 0 (OK), because it respects the conditions I need: B10=B9 and SUM(\$C\$8:D10)<e10
</e10
</e9
• <e9
<e10
F11 is 1 (OK), because it respects the conditions I need: B11<>B10 and SUM(\$C\$8:D11)<e11
</e11
</e10
</e9
• <e9
<e10
<e11
F12 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B12=B11 (TRUE) BUT SUM(\$C\$8:D12)<e12 (false)
</e12></e11
</e10
</e9
• <e9
<e10
<e11
<e12 (false)
F13 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B13=B12 (TRUE) BUT SUM(\$C\$8:D13)<e13 (false)
</e13></e12></e11
</e10
</e9
<e2
<e3
<e4
<e5
<e6 (false)
<e7 (false)
<e8), <strong=""><e9
<e10
<e11
<e12 (false)
<e13 (false)
OMG! I wrote it </e13></e12></e11
</e10
</e9
</e8),></e7></e6></e5
</e4
</e3
</e2

#### FormR

##### MrExcel MVP
Hi, I still don't really follow the rules, but..

<e2,if(b2=offset(b2,-1,0),0,1),"")
<e2,if(b2=offset(b2,-1,0),0,1),"")
But i need when it gets to another product, i need the sum to start from the line where it was changed.

The below formula does the sum and resets for new products, can you implement the rest yourself?

</e2,if(b2=offset(b2,-1,0),0,1),"")

</e2,if(b2=offset(b2,-1,0),0,1),"")

#### Sparcot

##### New Member

Corrected with "less than".

Actually it doesn't show me the values I need:

• F2 is 1 (OK), because it respects the conditions I need: B2<>B1 and SUM(\$C\$2:D2) "less than" D2<e2
</e2
• <e2
F3 is 0 (OK), because it respects the conditions I need: B3=B2 and SUM(\$C\$2:D3)<e3
</e3
</e2
"less than" D3
• <e2
F4 is 0 (OK), because it respects the conditions I need: B4=B3 and SUM(\$C\$2:D4)<e4
</e4
</e2
"less than" D4
• <e2
F5 is 1 (OK), because it respects the conditions I need: B5<>B4 and SUM(\$C\$2:D5)<e5
</e5
</e2
"less than" D5
• <e2
F6 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B6=B5 (TRUE) BUT SUM(\$C\$2:D6)<e6 (false)
</e6></e2
"less than" D6
• <e2
F7 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B7=B6 (TRUE) BUT SUM(\$C\$2:D7)<e7 (false)
</e7></e2
"less than" D7
• <e2
<e3
<e4
<e5
<e6 (false)
<e7 (false)
F8 is 0 (nOK - it has to be 1), because it SHOULD respect the conditions I need: B8<>B7 but the sum range should already start in \$C\$8 (SUM(\$C\$8:D8)<e8), <strong=""> "less than" D8 because we switched to ANOTHER PRODUCT</e8),></e7></e6></e5
</e4
</e3
</e2
• <e2
<e3
<e4
<e5
<e6 (false)
<e7 (false)
<e8), <strong=""></e8),></e7></e6></e5
</e4
</e3
</e2
F9 is 0 (OK), because it respects the conditions I need: B9=B8 and SUM(\$C\$8:D9)<e9
</e9
"less than" D9
• <e9
F10 is 0 (OK), because it respects the conditions I need: B10=B9 and SUM(\$C\$8:D10)<e10
</e10
</e9
"less than" D10
• <e9
F11 is 1 (OK), because it respects the conditions I need: B11<>B10 and SUM(\$C\$8:D11)<e11
</e11
</e9
"less than" D11
• <e9
F12 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B12=B11 (TRUE) BUT SUM(\$C\$8:D12)<e12 (false)
</e12></e9
"less than" D12
• <e9
F13 is 1 (nOK - it has to be blank), because it does not respect BOTH conditions I need: B13=B12 (TRUE) BUT SUM(\$C\$8:D13)</e9
"less than" D13

Last edited:

Anyone?

#### Sparcot

##### New Member
Worksheet Formulas
CellFormula
F2=SUMPRODUCT((A\$2:A2=A2)*(C\$2:D2))

</tbody>

<tbody>
</tbody>

OMG! That Works Perfect! Thanks.

Replies
3
Views
98
Replies
6
Views
139
Replies
3
Views
51
Replies
11
Views
511
Replies
2
Views
240

1,136,314
Messages
5,675,031
Members
419,544
Latest member
MVPDoug

### 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