Sum up if 2 conditions are met

[coolshawn]

Hi all,

I need help to write codes for the following situation.

[TABLE="width: 246"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Fix rebar[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Up to 12mm diameter.[/TD]
[TD] 37,060.00[/TD]
[/TR]
[TR]
[TD]16mm diameter.[/TD]
[TD] 224,740.00[/TD]
[/TR]
[TR]
[TD]20mm diameter.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25mm diameter.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32mm diameter.[/TD]
[TD] 7,140.00[/TD]
[/TR]
[TR]
[TD]Concrete Grade[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to sum up all values between fix rebar and concrete grade. First I need to find where fix rebar is, and then look for concrete grade. Once I know their positions, I need to find the sum of all items in between them. I have several hundreds of them in my database, and need to find sum for all of them. Anyone can help? I really appreciate it. Thanks in advance.

shawn

 

[admiral100]

Hi,

Assume that column A contains text, including Fix rebar and concrete grade
And column B contains the values.

C1 =ADDRESS(SUMPRODUCT((A1:A7="Fix rebar")*ROW(A1:A7)),ROW(A2),4)
D1 =ADDRESS(SUMPRODUCT((A1:A7="Concrete Grade")*ROW(A1:A7)),ROW(A2),4)
E1 to sum between them =SUM(INDIRECT(C1):INDIRECT(D1))

 

[Aladin Akyurek]

Something like...

=SUM(INDEX(B:B,MATCH("fix rebar",A:A,0)):INDEX(B:B,MATCH("concrete grade",A:A,0)))

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


WRONG FORMULA

 

[coolshawn]

hi admiral,

thank you for quick response. It worked for the 1st instance. but when i applied to the rest of the table, it did not produce results as I expected. It only picked up the first correct solution and carried it to the rest of the table. I would not dare to respond earlier because I did not know enough about address and sumproduct functions. I tried to expand the range you gave from A1:A7 to A1:A4890 but did not solve my problem. Can you please help to include the rest of the table? Thank you.

 

[coolshawn]

Hi Aladin,

Thank you for the quick response. The formula worked, but only for the first instance. When I expanded it to include the whole range, it did not produce the expected results.

I am attaching the expanded sample of my database to make my problem clearer.

[TABLE="width: 319"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]TASK[/TD]
[TD]COST
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD] Fix rebar
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Up to 12mm
[/TD]
[TD] 5,474.00
[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] 16mm
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD] 20mm
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] 25mm
[/TD]
[TD] 7,038.00
[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] 32mm
[/TD]
[TD] 99,314.00
[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD] 40mm
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD] Concrete Grade
[/TD]
[TD] 110,875.13[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD] Cutting off piles
[/TD]
[TD] 18,504.00[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD] Carry out MLT
[/TD]
[TD] 165,210.00[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD] subsequent MLT
[/TD]
[TD] 396,450.00[/TD]
[/TR]
[TR]
[TD="align: right"]12
[/TD]
[TD] Carried to Collection
[/TD]
[TD] 963,113.37
[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD] Carry out PDA
[/TD]
[TD] 22,900.00
[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD] 750mm Diameter Bored Pile
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15
[/TD]
[TD] Allow for mobilization
[/TD]
[TD] 51,540.00
[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD] Drill 750 mm diameter holes
[/TD]
[TD] 99,905.40
[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD] Drilled 750 mm raked holes
[/TD]
[TD] 94,937.80
[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD] 12 mm thick casing
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD] Fix rebar
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20
[/TD]
[TD] Up to 12mm
[/TD]
[TD] 14,416.00
[/TD]
[/TR]
[TR]
[TD="align: right"]23
[/TD]
[TD] 25mm
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD] 32mm
[/TD]
[TD] 119,646.00
[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD] 40mm
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD] Concrete Grade
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As it is shown in the sample, fix rebar appears twice - ID #1 and #1 9. The same thing with concrete grade. It is also listed twice - ID #8 and #26 .

So my problem is to find the sum of all values between these 2 conditions. Look for fix rebar and look for concrete grade. Then find the sum in between those two. And then continue down the table and look for the same conditions. Now in between those two conditions, I have many other rows with values, but I am not interested to find their sum. I am only looking for the sum whatever between fix rebar and concrete grade. The number of rows between those 2 conditions are not fixed. In the first instance, there are 6 rows to add, but in the second instance, only 4.

I hope I am making my problem more clear. Thank you for the help so far, and I hope someone can help me solve this problem.

Thank you.

shawn

 

[Aladin Akyurek]

What is or what are the expected result(s) for the larger sample you posted?

 

[coolshawn]

What is or what are the expected result(s) for the larger sample you posted?

For the 1st instance, 111,826 (5,474+7,038+99,314) --between ID 1 and 8
For the 2nd instance, 134,062 (14,416+119,646) --between ID 19 and 26.

 

[Aladin Akyurek]

For the 1st instance, 111,826 (5,474+7,038+99,314) --between ID 1 and 8
For the 2nd instance, 134,062 (14,416+119,646) --between ID 19 and 26.

Let A:C house the data, the row with headers included.

In E1 enter:

=COUNTIFS(B:B,"fix rebar")

In E2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$2:E2)>$E$1,"",SUM(INDEX($C$2:$C$25,SMALL(IF($B$2:$B$25="fix rebar",ROW($B$2:$B$25)-ROW($B$2)+1),ROWS($E$2:E2))):INDEX($C$2:$C$25,SMALL(IF($B$2:$B$25="concrete grade",ROW($B$2:$B$25)-ROW($B$2)+1),ROWS($E$2:E2)))))

Note that this set up assumes correct pairing, that is, fix rebar...concrete grade...fix rebar...concrete grade, etc., not sequences like ...concrete grade...fix rebar...fix rebar..., etc.

 

[admiral100]

Another option : Ctrl+Shift+Enter

C1 =SUM(INDEX($B$2:$B$25,SMALL(IF($A$2:$A$25="Fix rebar",ROW($A$2:$A$25),""),ROW(A1))-1):INDEX($B$2:$B$25,SMALL(IF($A$2:$A$25="Concrete Grade",ROW($A$2:$A$25),""),ROW(A1))-2))