Formulas

gdooley@jbelectric

I have a report where I track estimate by project manager. I would like to count the estimate that need follow up that are past due for each project manager. I keep getting an unparseable error

=countif([Number of Est]@row,{J&B Estimating Log Range 5},=<TODAY())

"Number of Est" is a reference to the sheet I am working on and the "J&B Estimating Log Range 5" is from other sheet.

Genevieve P.

Hi @gdooley@jbelectric

If you have multiple criteria that you're looking for (a Project Manager and an Overdue Date), then you'll want to use COUNTFS, plural.

A COUNTIFS Function works like this:

=COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

 

So in your case, something like this:

=COUNTIFS({J&B Estimating Log Project Manager Column}, "Project Manager Name", {J&B Estimating Log Range 5}, @cell <= TODAY())

If this hasn't helped, it would be useful to see a screen capture of the sheet you're referencing and your current sheet, but please block out sensitive data.

Cheers!

Genevieve

Nik Fuentes

for one, CountIf() only has two parameters, the range and the criterion to evaluate for each item in that range. If you delete the [Number of Est]@row part you should at least get past the #UNPARSABLE error, provided of course your range is the correct format/data type.

gdooley@jbelectric

it works that way and yes the UNPARSABLE is clear but it counts all the estimate in the log that are over due. I just want it to count the estimates that need follow up/over due for each Project Manager. Should I be using an IF / AND command

Genevieve P.

Hi @gdooley@jbelectric

If you have multiple criteria that you're looking for (a Project Manager and an Overdue Date), then you'll want to use COUNTFS, plural.

A COUNTIFS Function works like this:

=COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

 

So in your case, something like this:

=COUNTIFS({J&B Estimating Log Project Manager Column}, "Project Manager Name", {J&B Estimating Log Range 5}, @cell <= TODAY())

If this hasn't helped, it would be useful to see a screen capture of the sheet you're referencing and your current sheet, but please block out sensitive data.

Cheers!

Genevieve

gdooley@jbelectric

Genevieve P,

Thank you that worked perfect.