Excel/Google Sheets COUNTIFS formula to count rows, but with a substring in the Range.

[rolamento]

Hello all. I'm having a problem doing a simple COUNTIFS formula. I have to automate a spreadsheet that counts rows if it has the corresponding city, but only if the date is the date from another cell. Lemme post the table bellow:

Book1
	A	B	C	D	E
1	Datestamp	Location	CurrentDate	City	Count
2	21/07/2024 16:30	London	21/07/2024	London	0
3	21/07/2024 17:53	Paris		Paris	0
4	23/07/2024 19:53	Barcelona		New York	0
5				Mexico City	0
6				Barcelona	0
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=COUNTIFS(B:B,D2,A:A,"*" & $C$2 & "*")

The first 2 columns are the result of a Google Forms, to count the birth city of people attending a meeting. And I want to consolidate all people that attended said meeting in the last 2 columns, by city. Also, I want to only count given a certain day, so that I could change it on the go to see which people were present in any day of the results. I don't want to create another column with a substring or such, to avoid confusion.

So, basically, I would need, in this mock example, London and Paris to be at 1. If I change the date from "C2" to "23/07/2024", only Barcelona would count to 1. I thought about using something like "LEFT(A:A; 10)" to extract a substring from the Datestamp, so that it would match the date from C2, but it didn't work. Any help would be appreciated! Thanks in advance.

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	Datestamp	Location	CurrentDate	City	Count
2	7/21/24 16:30	London	7/21/2024	London	1
3	7/21/24 17:53	Paris		Paris	1
4	7/23/24 19:53	Barcelona		New York	0
5				Mexico City	0
6				Barcelona	0
Sheet9

Cell Formulas
Range	Formula
E2:E6	E2	=COUNTIFS($A$2:$A$10,">="&$C$2,$A$2:$A$10,"<"&$C$2+1,$B$2:$B$10,D2)

 

[Fluff]

How about

=SUMPRODUCT((INT($A$2:$A$100)=$C$2)*($B$2:$B$100=D2))

 

[rolamento]

How about

=SUMPRODUCT((INT($A$2:$A$100)=$C$2)*($B$2:$B$100=D2))

That works on Excel, given the dates are "Date" data types, but I would need it to work on Google Sheets. I tried to make it work there, but all the dates are in the string format, and when I try to apply that formula it doesn't work, saying that the INT() function doesn't accept strings :c

 

[rolamento]

How about

=SUMPRODUCT((INT($A$2:$A$100)=$C$2)*($B$2:$B$100=D2))

Nevermind, I figured it out >:D Thanks a lot! Just one more thing: Is there a way to make the search case insensitive? Like if a city is spelled "london" instead of "London".

 

[rolamento]

Try:

Book1
	A	B	C	D	E
1	Datestamp	Location	CurrentDate	City	Count
2	7/21/24 16:30	London	7/21/2024	London	1
3	7/21/24 17:53	Paris		Paris	1
4	7/23/24 19:53	Barcelona		New York	0
5				Mexico City	0
6				Barcelona	0
Sheet9

Cell Formulas
Range	Formula
E2:E6	E2	=COUNTIFS($A$2:$A$10,">="&$C$2,$A$2:$A$10,"<"&$C$2+1,$B$2:$B$10,D2)

Yo this one also worked! Thanks a lot! I just have to figure out how to make the City name to be case insensitive >.<

 

[Eric W]

Yo this one also worked! Thanks a lot! I just have to figure out how to make the City name to be case insensitive >.<

Glad we could help. In Excel, COUNTIFS is case insensitive. I can't comment on Google Sheets.

 

[Fluff]

Both countifs & sumproduct are case insensitive in Excel & Sheets.

 

[rolamento]

Both countifs & sumproduct are case insensitive in Excel & Sheets.

Right, thanks! I managed to make it work with all your help, but I would like a suggestion. If, instead of a single date to compare, I passed a string with two dates. How could I make it work to get all matching rows between that range, for example "22/07/2024 - 25/07/2024". How to get all matching rows between day 22 and 25?

 

[Fluff]

As that is a totally different question, it needs a new thread.
Thanks