Excel - Search entire column for string

[gfuentes4]

Hello,

I am not sure I am finding a way to do this online.

I want to count the number of times something happens each month by searing an entire column for a string value. If that specific string value is found, then check the corresponding column over to see if it matches a specific month, if it does, then add +1.

Example


This is where I would want everything to be copied to:

Incidents		Jul-17	Aug-17
Running		1	1
Jogging			1
Talking			1
This is where I would be comparing the data from a different sheet: Date Classification 7/16/2017 Running 8/22/2017 Running 8/28/2017 Jogging 8/31/2017 Talking <tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

Welcome to the board.

Try:

	A	B	C	D	E	F	G
1	Date	Classification			Incidents	Jul-17	Aug-17
2	7/16/2017	Running			Running	1	1
3	8/22/2017	Running			Jogging	0	1
4	8/28/2017	Jogging			Talking	0	1
5	8/31/2017	Talking

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula F2 =COUNTIFS(Sheet2!$B:$B,$E2,Sheet2!$A:$A,">="&F$1,Sheet2!$A:$A,"<="&EOMONTH(F$1,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put the formula in F2, and copy down and across as needed. The values in F1 and G1 are actual dates, the 1st of the month in each case, and just formatted to look like you want. Hope this helps.

 

[gfuentes4]

Welcome to the board.

Try:

	A	B	C	D	E	F	G
1	Date	Classification			Incidents	Jul-17	Aug-17
2	7/16/2017	Running			Running	1	1
3	8/22/2017	Running			Jogging	0	1
4	8/28/2017	Jogging			Talking	0	1
5	8/31/2017	Talking

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula F2 =COUNTIFS(Sheet2!$B:$B,$E2,Sheet2!$A:$A,">="&F$1,Sheet2!$A:$A,"<="&EOMONTH(F$1,0)) <tbody> </tbody>

<tbody>
</tbody>

Put the formula in F2, and copy down and across as needed. The values in F1 and G1 are actual dates, the 1st of the month in each case, and just formatted to look like you want. Hope this helps.

Thanks Eric for the reply!

Unfortunately, I was unable to get the result I wanted, either from incorrectly changing it to my needs or maybe I did not provide enough information of what I am attempting to do.

When I tried the formula you gave me I got #value error message.

 

[C Moore]

You only had 2 events in August and appear to be looking for a third.

 

[gfuentes4]

You only had 2 events in August and appear to be looking for a third.

Sorry, I forgot to clarify, the pictures are not complete representations of what actual data is there. Excuse the "DENIED" part.

I manually entered all of the numbers into the 1st table to show what I wanted it do look like.

As of now, it still does not work and I cannot seem to find anything that addresses this issue. I have spent a little over 2 hours searching.

Everything is either determining if a certain cell contains a certain date or similar, but nothing that addresses if a string AND date are both found then add to a counter on a separate sheet.

 

[Aladin Akyurek]

You need to post the sample input and the desired output in an Excel readable form (not as a picture or image).

 

[sunilbsrv2k]

Hi, Try this Add an extra column for second table that combines the first 2 columns Then use countif function the second table: Date Classification Combined 7/21/2017 Running Running Jul-17 8/13/2017 Jogging Jogging Aug-17 8/25/2017 Talking Talking Aug-17 9/27/2017 Cycling Cycling Sep-17 <colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"><colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"><colgroup><col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"><tbody> </tbody> 3rd coumn abve has the formula: =C2 &" " &TEXT(B2,"mmm-yy")
Classificatio Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Running 1 0 0 0 0 0 0 Jogging 0 1 0 0 0 0 0 Talking 0 1 0 0 0 0 0 Cycling 0 0 1 0 0 0 0 <colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"><colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"><colgroup><col width="64" style="width: 48pt;" span="6"><tbody> </tbody>
Cell which is intersection of "Running" and "Jul-17" in this table has the formula: =COUNTIF($D$2:$D$11, $B15& " " &TEXT(C$14, "mmm-yy"))

<tbody>
</tbody>

D2:D11 above corresponds to the column "Combined" in the first table...

 

[gfuentes4]

You need to post the sample input and the desired output in an Excel readable form (not as a picture or image).

Eric put it in an excel readable form, I believe?

Also, I cannot see the option to add a table when I am replying so could you help me do that so I can better present my problem I would greatly appreciate any help. Or if you could explain what you do not understand about the question or pictures that is unclear to you?

 

[gfuentes4]

Hi, Try this Add an extra column for second table that combines the first 2 columns Then use countif function the second table: Date Classification Combined 7/21/2017 Running Running Jul-17 8/13/2017 Jogging Jogging Aug-17 8/25/2017 Talking Talking Aug-17 9/27/2017 Cycling Cycling Sep-17 <tbody> </tbody> 3rd coumn abve has the formula: =C2 &" " &TEXT(B2,"mmm-yy")
Classificatio Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Running 1 0 0 0 0 0 0 Jogging 0 1 0 0 0 0 0 Talking 0 1 0 0 0 0 0 Cycling 0 0 1 0 0 0 0 <tbody> </tbody>
Cell which is intersection of "Running" and "Jul-17" in this table has the formula: =COUNTIF($D$2:$D$11, $B15& " " &TEXT(C$14, "mmm-yy"))

<tbody>
</tbody>

D2:D11 above corresponds to the column "Combined" in the first table...

Thank you very much for your reply! Just by reading over your response I can see it is not an approach I had thought of.

I apologize, I was replying to another user when you posted your response. Let me try your method and see if I can get it to work. Thanks once again!

 

[Aladin Akyurek]

What is the output? The upper or lower table?