Finding values in 2 columns

[Executioner]

Hi everyone,
I'm not really sure how to handle this problem. I tired the COUNTIF function, but I don't think its the correct one to use because the data is in another column.

In COLUMN F I have text data: E201, E202, E203, through E221 and ends at row 903.
In COLUMN C I have text data such as EDD2062647.

I want my formula to look in COLUMN F first for the values only: E207, E209, E212, E213, then go to COLUMN C and find all data that starts with EDD* and give me the total number of those records.

I also tried VLOOKUP, but I'm confused because of the 2 different criteria that are needed.

I'm using Excel version 2003.

 

[Aladin Akyurek]

Hi everyone,
I'm not really sure how to handle this problem. I tired the COUNTIF function, but I don't think its the correct one to use because the data is in another column.

In COLUMN F I have text data: E201, E202, E203, through E221 and ends at row 903.
In COLUMN C I have text data such as EDD2062647.

I want my formula to look in COLUMN F first for the values only: E207, E209, E212, E213, then go to COLUMN C and find all data that starts with EDD* and give me the total number of those records.

I also tried VLOOKUP, but I'm confused because of the 2 different criteria that are needed.

I'm using Excel version 2003.

Try...

=SUMPRODUCT(--ISNUMBER(MATCH($F$2:$F$903,List,0)),--ISNUMBER(SEARCH(" "&J2," "&$C$2:$C$903)))

List refers to a range housing the following items of interest: E207, E209, E212, E213 and J2 EDD.

 

[Executioner]

Try...

=SUMPRODUCT(--ISNUMBER(MATCH($F$2:$F$903,List,0)),--ISNUMBER(SEARCH(" "&J2," "&$C$2:$C$903)))

List refers to a range housing the following items of interest: E207, E209, E212, E213 and J2 EDD.

Thanks for the help.

I get 0, but I noticed you have &J2 in the formula. I tried to change it to F2 but I keep getting zero as the results.

 

[Aladin Akyurek]

Thanks for the help.

I get 0, but I noticed you have &J2 in the formula. I tried to change it to F2 but I keep getting zero as the results.

J2 is intended to house your criterion value of EDD.

List stands for a range that is intended to house your set of criteria consisting of: E207, E209, E212, and E213. The formula refers to List and to J2...

=SUMPRODUCT(--ISNUMBER(MATCH($F$2:$F$903,List,0)),--ISNUMBER(SEARCH(" "&J2," "&$C$2:$C$903)))

 

[markmzz]

Maybe this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #FFFF00;;">EDD2062647</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">E213</td><td style="text-align: center;;">E207</td><td style="text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">DD2062648</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E221</td><td style="text-align: center;;">E209</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">DD2062649</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E209</td><td style="text-align: center;;">E211</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">DD2062650</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E218</td><td style="text-align: center;;">E213</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">DD2062651</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E208</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">DD2062652</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E202</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">DD2062653</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E215</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">DD2062654</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E214</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">DD2062655</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E217</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">EDD2062656</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E208</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">EDD2062657</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E206</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">EDD2062658</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E203</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">DD2062648</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E218</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">DD2062649</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E209</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">DD2062650</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E217</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">DD2062648</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E216</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">DD2062649</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E211</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">DD2062650</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E206</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">DD2062651</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E221</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">DD2062652</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E205</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">DD2062653</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E203</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">DD2062654</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E208</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">DD2062655</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E216</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">EDD2062670</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E214</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">EDD2062671</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E221</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">MEDD2062654</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E206</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">DD2062655</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">E218</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;background-color: #FFFF00;;">EDD2062674</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">E211</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;background-color: #FFFF00;;">EDD2062675</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">E213</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet11</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">FIND(<font color="Green">"EDD",IF(<font color="Purple">ISNUMBER(<font color="Teal">FIND(<font color="#FF00FF">"EDD",C1:C29</font>)</font>),C1:C29,"REDD"</font>)</font>)=1</font>)*ISNUMBER(<font color="Red">MATCH(<font color="Green">F1:F29,G1:G4,0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[Executioner]

Thanks.

That does not work also. I did try the {} brackets by pressing ctrl-shft-enter.

I'll try to explain this again.

In column F2:F903
I have text values E201, E202, E203, E204 through E221

In column C2:C903
I have text values EDD2062647, EDD2052849, 20029, 18745, EDD2045678, etc

I need to have Excel 2003 select all the cells that have the E207, E209, E212, and E213 data values only. When those cells are found, what is the corresponding value in column C IF it has a value that starts with EDD only. Then count all of these values that have EDD and place the result in C916.

 

[Aladin Akyurek]

Thanks.

That does not work also. I did try the {} brackets by pressing ctrl-shft-enter.

I'll try to explain this again.

In column F2:F903
I have text values E201, E202, E203, E204 through E221

In column C2:C903
I have text values EDD2062647, EDD2052849, 20029, 18745, EDD2045678, etc

I need to have Excel 2003 select all the cells that have the E207, E209, E212, and E213 data values only. When those cells are found, what is the corresponding value in column C IF it has a value that starts with EDD only. Then count all of these values that have EDD and place the result in C916.

Enter in K2:K5 one by one: E207, E209, E212, and E213.

Enter in J2: EDD

Now try...

=SUMPRODUCT(--ISNUMBER(MATCH($F$2:$F$903,$K$2:$K$5,0)),--ISNUMBER(SEARCH(" "&J2," "&$C$2:$C$903)))

which will yield the count you desire.

 

[Executioner]

I think I solved my issue, but I used column G as an additional filter by using a 0 or a 1.

If column C has a value that begins with EDD, it will place a 1 there otherwise a 0.
=IF(LEFT(C2,3)="EDD",1,0)

I then totaled the values for each E201, E202, etc.
E201
E202
E203
E204
E205
E207
E208
E209
E210
E210A
E210B
E211B
E212
E213
E214
E215
E216
E220
E221
=COUNTIF($F$2:$F$903,"E201") I changed the "E201" to the items listed above to get a total count of those items for each E value.

In the new column G, I add this formula:=SUMPRODUCT(--(F$2:F$903=E907),--(G$2:G$903=1))
That tells me how many of those EDD's are on each one of those E201, E202, etc. I then simply added the E207, E209, E212, and E213 together: =SUM(G912,G914,G919,G920)

value = 180

I would like to thank you guys for your efforts, and if there is a single formula that can do this, I still would be interested.

 

[Executioner]

Enter in K2:K5 one by one: E207, E209, E212, and E213.

Enter in J2: EDD

Now try...

=SUMPRODUCT(--ISNUMBER(MATCH($F$2:$F$903,$K$2:$K$5,0)),--ISNUMBER(SEARCH(" "&J2," "&$C$2:$C$903)))

which will yield the count you desire.

YES! This worked also. Much appreciated.

 

[Aladin Akyurek]

YES! This worked also. Much appreciated.

You are welcome. Thanks for providing feedback.