Edit - Stack Overflow

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

Rev

Required fields*

COUNTIF using DATE only and name match criteria

I have been tasked to count how many leads does a sales agents takes everyday. So I created a spreadsheet, where the data is being auto populated from a CRM platform, and a separate tab for the tracker.

I use 2 criteria for countifs, the date and the agent's name. I have no problem with the Agent's Name. The only problem is the DATE.

The problem, Countif doesn't count cells because it can't match my cell reference (date only) where as in leads data, it contains both date and time.

Also, the leads data has to be on a separate tab, I can't combine the two tabs, it is meant to be hidden and locked, as data in this is being automatically populated by a CRM platform.

I created a similar spreadsheet as an example. Here is the link. https://docs.google.com/spreadsheets/d/1-kEKHCQk1qF_k8oPnu-166fYdHukozfChEQ4bixwwB0/edit?usp=sharing

Any guidance or help on this is appreciated.

Answer*

You could also have a more structured data summary using the following:

You can select the month and/or enter the year to filter the data accordingly:

[![enter image description here](https://i.sstatic.net/0b23SRMC.png)](https://i.sstatic.net/0b23SRMC.png)

```
=LET(
  data, 'Leads Data'!A2:B,
  m, MONTH(DATEVALUE(B1 & " 1")),
  y, VALUE(Q1),
  nm, INDEX(data,,2),
  dt, INDEX(data,,1),
  uniq, UNIQUE(nm),
  names, SORT(FILTER(uniq, uniq <> "")),
  cnt, MAKEARRAY(ROWS(names), 31,
        LAMBDA(r, c,
          COUNTIFS(nm, INDEX(names, r, 1),
                   dt, ">=" & DATE(y, m, c),
                   dt, "<" & DATE(y, m, c) + 1)
        )
      ),
  rs, BYROW(cnt, LAMBDA(row, SUM(row))),
  ct, BYCOL(cnt, LAMBDA(col, SUM(col)))
, VSTACK(
      HSTACK(names, cnt, rs),
      HSTACK(ROWS(names), ct, SUM(rs))
   )
)
```

The necessary conditional formattings have been applied to improve the summary

# **UPDATE:**

OP: wanted a Weekly Report given a starting date (Monday)  
  
[![enter image description here](https://i.sstatic.net/1f4FAS3L.png)](https://i.sstatic.net/1f4FAS3L.png)

```
=LET(
  raw, UNIQUE(Sample!E2:E),
  names, SORT(FILTER(raw, raw<>"")),
  counts, MAKEARRAY(ROWS(names),7,LAMBDA(r,c,COUNTIFS(Sample!E2:E,INDEX(names,r),Sample!B2:B,">="&($D$1+c-1),Sample!B2:B,"<"&($D$1+c)))),
  rowTotals, BYROW(counts,LAMBDA(r,SUM(r))),
  dataBlock, HSTACK(names,counts,rowTotals),
  totals, HSTACK(ROWS(names),BYCOL(counts,LAMBDA(c,SUM(c))),SUM(rowTotals)),
  VSTACK(dataBlock,totals)
)
```

Edit Summary*

Cancel

Please do not upload images of code/data/errors.
– TheMaster
Commented Apr 10 at 9:25
It works! Thank you so much!
– Ezio
Commented Apr 10 at 12:00

Add a comment |

Correct minor typos or mistakes
Clarify meaning without changing it
Add related resources or links
Always respect the author’s intent
Don’t use edits to reply to the author

create code fences with backticks ` or tildes ~
```
like so
```
add language identifier to highlight code
```python
def function(foo):
print(foo)
```
put returns between paragraphs
for linebreak add 2 spaces at end
_italic_ or **bold**
indent code by 4 spaces
backtick escapes `like _so_`
quote by placing > at start of line
to make links (use https whenever possible)

<https://example.com>

[example](https://example.com)

<a href="https://example.com">example</a>

formatting help »
answering help »

Collectives™ on Stack Overflow

COUNTIF using DATE only and name match criteria

Answer*