How to sum values in column D until a blank cell is reached and if values in column C equals "WP" or "BS"

[jfin1ty]

Hi everyone,
I'm hoping you can help me with an excel formula to solve a problem I'm stuck on.
I think I am pretty close to solution but just can't crack what it is I need to make the formula work how I would like.
Basically, the problem is this:
My formula is pasted all down column B. I need to sum all the values in column D up until a blank cell is reached. On top of this, I only want it to sum the values in column D until a blank cell is reached in column C and only if the values in column C equal "WP" or "BS". If a value in column C equals anything else, then I don't need it added to the sum of column D.
To round it all off, I need it to only sum the values in column D is column A is not blank.

I got as far as the below, but this formula just counts all values in column D until a blank cell is reached as long as the value in column A is not blank.
=IF(A1<>"",SUM(D1:INDEX(D1:$D$1000,MATCH(TRUE,(D2:$D$1000=""),0))),"")

I need to incorporate this formula into a sheet that doesn't really have room for helper columns and the like so if its possible to work it into one formula to copy down the whole B column that would be great.

 

[Peter_SSs]

Welcome to the MrExcel board!

Would something like this work for you?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 05 15.xlsm
	A	B	C	D
1	12	14.012	WP	0.386
2			WP	2.895
3			WP	0.96
4			WP	1.926
5			WP	3.895
6			WP	1.95
7			RTW	2
8
9	12.18	15.003	WP	5.245
10			WP	0.944
11			WP	5.814
12			WP	2
13			RTW	1
14
15	12.18	10.068	WP	3.24
16			WP	1
17			WP	1.058
18			Wp	1.672
19			WP	3.098
20
21	23	22.623	WP	7.889
22			WP	1.096
23			WP	3.168
24			WP	8.555
25			Wp	1.915
26
27	27.5	30.156	WP	11.161
28			WP	13.07
29			Wp	5.925
Sum Ranges

Cell Formulas
Range	Formula
B1:B29	B1	=IF(A1="","",SUM(D1:D$100)-SUM(B2:B$100))

 

[jfin1ty]

Welcome to the MrExcel board!

Would something like this work for you?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 05 15.xlsm
	A	B	C	D
1	12	14.012	WP	0.386
2			WP	2.895
3			WP	0.96
4			WP	1.926
5			WP	3.895
6			WP	1.95
7			RTW	2
8
9	12.18	15.003	WP	5.245
10			WP	0.944
11			WP	5.814
12			WP	2
13			RTW	1
14
15	12.18	10.068	WP	3.24
16			WP	1
17			WP	1.058
18			Wp	1.672
19			WP	3.098
20
21	23	22.623	WP	7.889
22			WP	1.096
23			WP	3.168
24			WP	8.555
25			Wp	1.915
26
27	27.5	30.156	WP	11.161
28			WP	13.07
29			Wp	5.925
Sum Ranges

Cell Formulas
Range	Formula
B1:B29	B1	=IF(A1="","",SUM(D1:D$100)-SUM(B2:B$100))

Hi Peter,
I tried to utilise the addin but the options were greyed out for me. Likely a setting I can’t change due to being on the work computer.

Unfortunately I don’t think your solution takes into account the requirement of not summing the values from column D with RTW or RTB in column C next to them. Any idea how to include that into the formula?
Thanks.

 

[Peter_SSs]

I tried to utilise the addin but the options were greyed out for me.

See if this helps: Xl2bb is disabled

Unfortunately I don’t think your solution takes into account the requirement of not summing the values from column D with RTW or RTB in column C next to them.

Yes, sorry, I did not take that in properly. Is this any better?

23 05 15.xlsm
	A	B	C	D
1	12	12.012	WP	0.386
2			WP	2.895
3			BS	0.96
4			WP	1.926
5			WP	3.895
6			WP	1.95
7			RTW	2
8
9	12.18	14.003	WP	5.245
10			WP	0.944
11			WP	5.814
12			WP	2
13			RTW	1
14
15	12.18	0	XX	3.24
16			XX	1
17			XX	1.058
18			XX	1.672
19			XX	3.098
20
21	23	3.011	yy	7.889
22			BS	1.096
23			yy	3.168
24			yy	8.555
25			Wp	1.915
26
27	27.5	30.156	WP	11.161
28			WP	13.07
29			Wp	5.925
Sum Ranges

Cell Formulas
Range	Formula
B1:B29	B1	=IF(A1="","",SUM(SUMIF(C1:C$100,{"WP","BS"},D1:D$100))-SUM(B2:B$100))

 

[jfin1ty]

See if this helps: Xl2bb is disabled


Yes, sorry, I did not take that in properly. Is this any better?

23 05 15.xlsm
	A	B	C	D
1	12	12.012	WP	0.386
2			WP	2.895
3			BS	0.96
4			WP	1.926
5			WP	3.895
6			WP	1.95
7			RTW	2
8
9	12.18	14.003	WP	5.245
10			WP	0.944
11			WP	5.814
12			WP	2
13			RTW	1
14
15	12.18	0	XX	3.24
16			XX	1
17			XX	1.058
18			XX	1.672
19			XX	3.098
20
21	23	3.011	yy	7.889
22			BS	1.096
23			yy	3.168
24			yy	8.555
25			Wp	1.915
26
27	27.5	30.156	WP	11.161
28			WP	13.07
29			Wp	5.925
Sum Ranges

Cell Formulas
Range	Formula
B1:B29	B1	=IF(A1="","",SUM(SUMIF(C1:C$100,{"WP","BS"},D1:D$100))-SUM(B2:B$100))

Works great thanks mate! One last question. Could I take your formula and put it in conditional formatting so I could do away with column B entirely? For example, if A1 is 12 and the formula calculates the value from column D and the result is greater than A1, have A1 turn red.

 

[jfin1ty]

Works great thanks mate! One last question. Could I take your formula and put it in conditional formatting so I could do away with column B entirely? For example, if A1 is 12 and the formula calculates the value from column D and the result is greater than A1, have A1 turn red.

All good Peter. I worked out the conditional formatting side of things in the end. I just had to change the WP and BS array part of the formula to get it to work so now it evaluates WP separately from BS.

=IF(A1="","",SUM(SUMIF(C1:C$100,"WP",D1:D$100)+SUMIF(C1:C$100,"BS",D1:D$100))-SUM(B2:B$100))>$A1

 

[jfin1ty]

All good Peter. I worked out the conditional formatting side of things in the end. I just had to change the WP and BS array part of the formula to get it to work so now it evaluates WP separately from BS.

=IF(A1="","",SUM(SUMIF(C1:C$100,"WP",D1:D$100)+SUMIF(C1:C$100,"BS",D1:D$100))-SUM(B2:B$100))>$A1

Actually, scrap that Peter. I just realized that if I now get rid of column B, the formatting does not work. Im assuming this is because of this part of the formula coming into play

-SUM(B2:B$100)

 

[Peter_SSs]

What about using something like this? (and just hiding column B, maybe move it somewhere to the right first)

23 05 15.xlsm
	A	B	C	D
1	12	12.012	WP	0.386
2			WP	2.895
3			BS	0.96
4			WP	1.926
5			WP	3.895
6			WP	1.95
7			RTW	2
8
9	12.18	14.003	WP	5.245
10			WP	0.944
11			WP	5.814
12			WP	2
13			RTW	1
14
15	12.18	0	XX	3.24
16			XX	1
17			XX	1.058
18			XX	1.672
19			XX	3.098
20
21	23	3.011	yy	7.889
22			BS	1.096
23			yy	3.168
24			yy	8.555
25			Wp	1.915
26
27	27.5	30.156	WP	11.161
28			WP	13.07
29			Wp	5.925
Sum Ranges (2)

Cell Formulas
Range	Formula
B1:B29	B1	=IF(A1="","",SUM(SUMIF(C1:C$100,{"WP","BS"},D1:D$100))-SUM(B2:B$100))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A29	Expression	=B1>A1	text	NO

 

[jfin1ty]

What about using something like this? (and just hiding column B, maybe move it somewhere to the right first)

23 05 15.xlsm
	A	B	C	D
1	12	12.012	WP	0.386
2			WP	2.895
3			BS	0.96
4			WP	1.926
5			WP	3.895
6			WP	1.95
7			RTW	2
8
9	12.18	14.003	WP	5.245
10			WP	0.944
11			WP	5.814
12			WP	2
13			RTW	1
14
15	12.18	0	XX	3.24
16			XX	1
17			XX	1.058
18			XX	1.672
19			XX	3.098
20
21	23	3.011	yy	7.889
22			BS	1.096
23			yy	3.168
24			yy	8.555
25			Wp	1.915
26
27	27.5	30.156	WP	11.161
28			WP	13.07
29			Wp	5.925
Sum Ranges (2)

Cell Formulas
Range	Formula
B1:B29	B1	=IF(A1="","",SUM(SUMIF(C1:C$100,{"WP","BS"},D1:D$100))-SUM(B2:B$100))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A29	Expression	=B1>A1	text	NO

Legend! Works perfectly.

 

[Peter_SSs]

Glad to hear it. Thanks for the follow-up.