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:
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.
| 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.
