Sum Up Cells That Contain The Letter "D" - Not SUMIF

[fun2excel4money]

Hello All,

I have a simple data set with two columns shown below. The first column contains an amount. The second column has a corresponding value that can either be "D", "R", "S", or any combination of the three letters. I am looking for a formula that can sum up the values where the second column contains a "D". I am trying to avoid CSE.


<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>[TABLE="class: grid, width: 130"]
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65, align: right"]1[/TD]
[TD="width: 65"]D[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]SD[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]DSR[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]DSR[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]S[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

Based on a regular sum, the total should be 67. I am currently using this formula: <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style>SUMPRODUCT((IFERROR(IF(SEARCH("d",B1:B17)>0,1,0),0)*A1:A17). This provides me a number of 153. If I use CSE, the amount populates to 67. I believe I have to use CSE because of the array in the search. If there is a similar solution without the use of CSE, I would love to know. Thank you very much in advance for your help!

 

[MARK858]

Why does SUMIF not work (see below)?

Excel 2010
	A	B	C	D
1	1	D		67
2	2	D
3	3	D
4	4	D
5	5	D
6	6	D
7	7	S
8	8	S
9	9	SD
10	10	DSR
11	11	R
12	12	R
13	13	DSR
14	14	D
15	15	S
16	16	R
17	17	S
Sheet3

Cell Formulas
Range	Formula
D1	=SUMIF(B1:B17,"*D*",A1:A17)

 

[jtakw]

Hi,

Modified SUMPRODUCT formula:

Book1
	A	B	C	D
1	1	D		67
2	2	D
3	3	D
4	4	D
5	5	D
6	6	D
7	7	S
8	8	S
9	9	SD
10	10	DSR
11	11	R
12	12	R
13	13	DSR
14	14	D
15	15	S
16	16	R
17	17	S
Sheet36

Cell Formulas
Range	Formula
D1	=SUMPRODUCT((ISNUMBER(SEARCH("D",B1:B17))*A1:A17))

 

[fun2excel4money]

I see what you did. Do the asterisks mean "contain"?

 

[jtakw]

I see what you did. Do the asterisks mean "contain"?

Wildcard.

 

[MARK858]

They are wildcards meaning any character,

 

[fun2excel4money]

Awesome! Thanks for both solutions :D. Added more to the arsenal!

 

[jtakw]

You're welcome, thought you had some kind of reason for not using SUMIF since you had it in the thread Title. Mark's SUMIF is better than using SUMPRODUCT.