Before you start
Build the dashboard on a separate sheet (tab) rather than adding formulas directly into Contacts or Companies — a stray formula there can shift column positions and cause sync errors.
Confirm whether Next Action Date (column J) and Created Date (column M) are actually recognized as dates in Google Sheets. A cell right-aligned by default is being read as a date; left-aligned means it’s stored as plain text and date comparisons (before/after) won’t work correctly. If it’s text, either reformat the cell as a date or wrap it with DATEVALUE() in the formula.
The formulas here are a working pattern. Try them against a handful of test rows and check the result by eye before applying them to real data.
Counts by status
The easiest approach is a pivot table: select the Contacts range, Insert → Pivot table, set rows to Status (column H) and values to Name (column A, summarized as COUNT). Check the sheet’s dropdown for the actual set of status values in use.
To build it as a formula instead:
=QUERY(Contacts!A2:T, "select H, count(A) where H is not null group by H label count(A) 'Count'")Counts by assignee
Same idea as the status count, with the pivot table’s row set to Assignee (column I) instead. As a formula:
=QUERY(Contacts!A2:T, "select I, count(A) where I is not null and H <> 'Deleted' group by I label count(A) 'Count'")This week’s follow-ups
Lists contacts whose Next Action Date (column J) falls within the next 7 days.
=FILTER(Contacts!A2:O, Contacts!J2:J<>"", Contacts!J2:J>=TODAY(), Contacts!J2:J<=TODAY()+7, Contacts!H2:H<>"Deleted")If this comes back empty, check first whether column J is actually recognized as a date (see the section above).
Overdue next actions
Lists contacts whose next action date has passed and haven’t been marked done (not Lost or Deleted).
=FILTER(Contacts!A2:O, Contacts!J2:J<>"", Contacts!J2:J<TODAY(), Contacts!H2:H<>"Deleted", Contacts!H2:H<>"Lost")New contacts this month
Counts contacts added this month using Created Date (column M). This column is written automatically by the PixWork app, so if it isn’t recognized as a date, apply the fix from the first section.
=COUNTIFS(Contacts!M2:M, ">="&EOMONTH(TODAY(),-1)+1, Contacts!M2:M, "<="&TODAY())Turning a table into a chart
Once you have a count table, select the range and use Insert → Chart to turn it into a pie or bar chart. A pie chart tends to read well for counts by status; a bar chart works better for counts by owner or new contacts per month.
Lead counts by event work the same way — pivot or QUERY on Event Name (column K) instead, using the same pattern as counts by status.