Counting and Dates

Trooptho

Hello,

 

I have been trying to come up with a formula that works. I am trying to count all of the "request" I have through a certain date. The form that I am using to take requests and the sheet that I am tracking metrics are separate.

=IF(AND({Intake}) > DATE(2018, 12, 1), {Intake} < DATE(2018, 12, 31))

This is what I am trying to make work.

 

The second question would be how could I then be able to count how many requests from a certain vendor during the month. On my intake form it has the requestor and date requested. I am trying to pull all of this information on a dashboard

 

Thank you for the help in advance

Paul Newcome

Try this for counting through certain dates:

 

=COUNTIFS({Intake Range 1}, @cell > DATE(2018, 12, 01), {Intake Range 1}, @cell < DATE(2018, 12, 31)

 

{Intake Range 1} would be the date column in your other sheet.

 

For the vendor specific per month, you could use something along the lines of:

 

=COUNTIFS({Intake Range 1}, MONTH(@cell) = 12, {Intake Range 1}, YEAR(@cell) = 2018, {Intake Range 2}, Enter Vendor's name here or cell referencing the vendor's name)

 

{Intake Range 1} is the same as above.

 

{Intake Range 2} is the column in the other sheet referencing the vendor's name.

 

The criteria for {Intake Range 2} could be entered in two different ways depending on how your data is being displayed.

 

1. You could enter the vendors name in quotes "Vendor's Name" to search for that exact text.

 

2. If your counts are being displayed as a table with the vendor's name in another column on that same sheet in the same row, you could reference it as [Vendor Column Name]@row.

Trooptho

I tried using:

=COUNTIF({Intake} @cell > DATE(2018, 12, 1),{Intake}, @cell < DATE(2018, 12, 31))

 

Coming up unparsable. I should mention that I am referencing a column that is a date column via the smartsheet form.

 

 

Paul Newcome

You need a comma between the first {Intake} and the first @cell

 

@{Intake}, @cell

Trooptho

Thanks in advance for the assistance Paul,

=COUNTIF({Intake}, @cell > DATE(2018, 12, 1), {Intake}, @cell < DATE(2018, 12, 31))

 

is now throwing incorrect argument set?

Paul Newcome

You should be using COUNTIFS instead of COUNTIF since you have multiple sets of criteria.