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. Do not judge this by left or right alignment — alignment can be set by formatting, so a right-aligned cell may still be text. Check it with a formula instead:
=ISNUMBER(Contacts!J2)
=ISNUMBER(Contacts!M2)TRUE means the value is a real date (a number) and date comparisons will work. FALSE means it is text — reformat the cell as a date, or wrap it with DATEVALUE() in the formula. To check a whole column, see whether =COUNTA(Contacts!J2:J)-COUNT(Contacts!J2:J) returns 0; anything else means some rows are text.
If a formula returns an error
When a formula from this page errors on paste, or comes back empty, check these:
- Argument separators are locale-dependent — some locales use
;rather than, - Status values are matched in English — if the sheet uses translated status values, a condition like
<>"Deleted"will not match - Column letters assume the default layout — if you have reordered the Contacts columns, adjust the H, I, J, and M references to match your own sheet
- Dates stored as text cannot be compared — check them with the
ISNUMBER()formula in the section above
Try a formula against a handful of test rows and check the result before applying it to real data. Build the dashboard on a separate sheet (tab) from Contacts and Companies.
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. Deleted marks contacts already deleted inside the app, so it is excluded from the counts:
=QUERY(Contacts!A2:T, "select H, count(A) where H is not null and H <> 'Deleted' group by H label count(A) 'Count'")To see counts that include Deleted, drop the and H <> 'Deleted' clause — and say so in the heading of the resulting table, so nobody reads it as a count of live contacts.
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'")Follow-ups due in the next 7 days
Lists contacts whose Next Action Date (column J) falls within the next 7 days. That is seven days including today (today plus the following six), not the calendar week. For a Monday-to-Sunday window, use the next section.
=FILTER(Contacts!A2:O, Contacts!J2:J<>"", Contacts!J2:J>=TODAY(), Contacts!J2:J<=TODAY()+6, Contacts!H2:H<>"Deleted")It is TODAY()+6, not TODAY()+7. Both ends of the comparison are inclusive, so +7 would cover eight days counting from today. If this comes back empty, check first whether column J is actually recognized as a date (the ISNUMBER() check above).
Using a calendar week (Monday to Sunday)
Use this instead when you want a Monday-start week rather than "seven days from today". WEEKDAY(TODAY(),3) counts Monday as 0, so subtracting it from today lands on that week’s Monday.
=FILTER(Contacts!A2:O, Contacts!J2:J<>"", Contacts!J2:J>=TODAY()-WEEKDAY(TODAY(),3), Contacts!J2:J<=TODAY()-WEEKDAY(TODAY(),3)+6, Contacts!H2:H<>"Deleted")For a Sunday-start week, use WEEKDAY(TODAY(),1)-1 instead. Either way the end is six days after the start.
Overdue next actions
Lists contacts whose next action date has passed and are still open. Won and Lost are excluded alongside Deleted: a deal that has already closed either way is not an overdue follow-up.
=FILTER(Contacts!A2:O, Contacts!J2:J<>"", Contacts!J2:J<TODAY(), Contacts!H2:H<>"Deleted", Contacts!H2:H<>"Lost", Contacts!H2:H<>"Won")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.