COUNTIF using DATE only and name match criteria

Question

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 1

You may try this formula

=BYCOL(B$1:E$1, LAMBDA(date, ARRAYFORMULA(IF(A2:A13="", "", COUNTIFS('Leads Data'!$B:$B, A2:A13, 'Leads Data'!$A:$A, ">="&date, 'Leads Data'!$A:$A, "<"&date+1)))))

I just added some dates to the given spreadsheet so that I can test if it's working, I used COUNTIF to check the name and date range for each row, and using the IF function to avoid getting zero for empty rows, and ARRAYFORMULA for all the names (A2:A13) at once, lastly the BYCOL function to get the column by column so that you can have single formula that fills the whole table automatically.

	04/04/2025	05/04/2025	06/04/2025	07/04/2025
Julia	8	1	2	2
Alex	2	1	1	1
Andrew	5	2	1	1
Ash	4	1	1	1
Erin	3	1	1	1
John	4	1	1	3
Josh	3	2	2	2
Julia	8	1	2	2
Ray	5	1	2	1
Sam	5	1	1	2
Steve	2	1	1	2
Trent	5	3	3	1

Answer 2

Use query(), like this:

=query(
  'Leads Data'!A1:B,
  "select Col2, count(Col2) 
   where Col2 is not null
   group by Col2
   pivot toDate(Col1)",
  1
)

See query().

Answer 3

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:

=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)

=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)
)