fun2excel4money
New Member
- Joined
- Dec 24, 2016
- Messages
- 18
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>
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
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!
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>
1 | D |
2 | D |
3 | D |
4 | D |
5 | D |
6 | D |
7 | S |
8 | S |
9 | SD |
10 | DSR |
11 | R |
12 | R |
13 | DSR |
14 | D |
15 | S |
16 | R |
17 | S |
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
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!