Count if based off of a date range in another column

[Lea]

Hello,
I hope to get some help with a formula. The part I can get is this-
=COUNTIF(C:C,"*Philip*")

The part I can't figure out is how to do that based off of a date range in column B.
I would like the formula to count how many times *Philip* appears in column C for the month of January which is in column B.
The date format is 01/01/2023

Thank you for any help,
Lea

 

[awoohaw]

This uses matrix math. but answers your question, i think:

mr excel questions 21.xlsm
	A	B	C	D	E
1		Date	Names
2		2023-01-01	Tom	Name:	Philip
3		2023-01-01	Philip	Date:	2023-01-01
4		2023-02-01	Mary
5		2023-03-01	Bob	Count:	3
6		2023-01-01	Philip
7		2023-02-01	Carol
8		2023-03-01	Alice
9		2023-04-01	Philip
10		2023-01-01	Philip
Sheet7

Cell Formulas
Range	Formula
E5	E5	=SUM((--(E2=$C$2:$C$10))*(--(E3=$B$2:$B$10)))

 

[awoohaw]

if you want wildcard name search use this:

mr excel questions 21.xlsm
	D	E
6	Count w Name Wildcard:	3
Sheet7

Cell Formulas
Range	Formula
E6	E6	=SUM((--(ISNUMBER(FIND(E2,$C$2:$C$10))))*(--(E3=$B$2:$B$10)))

 

[awoohaw]

note: the above are using Excel 365, if you are using something else, you may need to change the SUM functions to SUMPRODUCT or Use CNTL-SHFT-ENTER to enter the formulas.

 

[kevin9999]

Another option....

Book1
	B	C	D	E
1	HDR1	HDR2
2	01/01/2023	Philip Smith	Philip in January =	6
3	04/01/2023	I'm Philip
4	07/01/2023	Jones
5	10/01/2023	Philip Smith
6	13/01/2023	Philip
7	16/01/2023	Adam
8	19/01/2023	Eve
9	22/01/2023	Philip
10	25/01/2023	Adam
11	28/01/2023	Eve
12	31/01/2023	I'm Philip
13	03/02/2023	I'm Philip
14	06/02/2023	I'm Philip
15	09/02/2023	Jack
16	12/02/2023	Philip Jones
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(1*(MONTH(B2:B1000)=1)*(ISNUMBER(SEARCH("Philip",C2:C1000))))

 

[Lea]

=SUMPRODUCT(1*(MONTH(B2:B1000)=1)*(ISNUMBER(SEARCH("Philip",C2:C1000))))

My apologies to all- I am in the lab by myself tonight and I got very busy with samples. I will try the suggestions out tomorrow. I thank you for the replies and assistance. I really appreciate it!
Lea

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option is

=COUNTIFS(B:B,">="&DATE(2023,1,1),B:B,"<"&DATE(2023,2,1),C:C,"*Philip*")

 

[Lea]

Another option....

Book1
	B	C	D	E
1	HDR1	HDR2
2	01/01/2023	Philip Smith	Philip in January =	6
3	04/01/2023	I'm Philip
4	07/01/2023	Jones
5	10/01/2023	Philip Smith
6	13/01/2023	Philip
7	16/01/2023	Adam
8	19/01/2023	Eve
9	22/01/2023	Philip
10	25/01/2023	Adam
11	28/01/2023	Eve
12	31/01/2023	I'm Philip
13	03/02/2023	I'm Philip
14	06/02/2023	I'm Philip
15	09/02/2023	Jack
16	12/02/2023	Philip Jones
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(1*(MONTH(B2:B1000)=1)*(ISNUMBER(SEARCH("Philip",C2:C1000))))

Hi kevin9999,

Thank you for the help. Your formula worked for me. I thank you for your time.

Kind regards- Lea

 

[Lea]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option is

=COUNTIFS(B:B,">="&DATE(2023,1,1),B:B,"<"&DATE(2023,2,1),C:C,"*Philip*")

Hello Fluff,

Your formula worked as well as kevin9999. And thank you for the tip about updating my profile. I have done as you suggested.

Kind regards- Lea

 

[Lea]

This uses matrix math. but answers your question, i think:

mr excel questions 21.xlsm
	A	B	C	D	E
1		Date	Names
2		2023-01-01	Tom	Name:	Philip
3		2023-01-01	Philip	Date:	2023-01-01
4		2023-02-01	Mary
5		2023-03-01	Bob	Count:	3
6		2023-01-01	Philip
7		2023-02-01	Carol
8		2023-03-01	Alice
9		2023-04-01	Philip
10		2023-01-01	Philip
Sheet7

Cell Formulas
Range	Formula
E5	E5	=SUM((--(E2=$C$2:$C$10))*(--(E3=$B$2:$B$10)))

Hello awoohaw,

Thank you for your taking your time to help me. I was having difficulty making your formula work for me as I am really a novice at this. I saw it worked in your example, but I don't have the excel skills to adapt it to my spreadsheet.
I appreciate your help.

Lea