Count in between two dates

Lakshman .

Hi guys

I am trying to count number of inspection submissions in each month, I tried some of the answers form previous discussions but it did not work for me. can some one help me?

Paul Newcome

Try something along the lines of...

=COUNTIFS([Inspection date]:[Inspection date], AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####))

Just replace "##" and "####" with the appropriate month and year numbers you want to count for.

Werner Gerstacker

If you want to simply show the number of entries for each of the inspection dates your formula in a new text/number column should be:

=COUNTIF([Inspection date]:[Inspection date],[Inspection date]@row)

But this will result in e.g. 3 entries for the first three rows.

You might want to do this in a separate section of the sheet or maybe even a separate sheet altogether that only holds one line per date to avoid these multiple entries for the same date.

Lakshman .

I want to count number of submissions in month of may 2020 & in month of April 2020.... and no of submissions in that particular month in future also based on month.

Lakshman .

https://community.smartsheet.com/discussion/3718/how-to-count-quantity-of-date-entries-within-a-month-time-frame

I tried to follow the attached post but it did not work for me, but i wanted to do the exact same thing.

Lakshman .

https://community.smartsheet.com/discussion/3718/how-to-count-quantity-of-date-entries-within-a-month-time-frame

Tried to same as this article but it did not work for me

Werner Gerstacker

Sorry, I clearly didn't read carefully enough 😒

Here's the formula for April

=COUNTIF([Inspection date]:[Inspection date], IFERROR(MONTH(@cell), 0) = 4)

And here's the one for May:

=COUNTIF([Inspection date]:[Inspection date], IFERROR(MONTH(@cell), 0) = 5)

If you don't want to count the entry for April 2nd you need to have it excluded through a 2nd condition in a COUNTIFS function that refers to the cell that results in the exclusion.

Lakshman .

Thanks for the formula, that worked, but i did not understand this

If you don't want to count the entry for April 2nd you need to have it excluded through a 2nd condition in a COUNTIFS function that refers to the cell that results in the exclusion.

but i tried i did not worked got error in linking

Paul Newcome

Try something along the lines of...

=COUNTIFS([Inspection date]:[Inspection date], AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####))

Just replace "##" and "####" with the appropriate month and year numbers you want to count for.

Lakshman .

Got same count, with both formulas

Werner Gerstacker

There is probably a reason why the April 2nd date is formatted with strikethrough - maybe a conditional format base on a column called 'Status' with an entry 'Not Submitted' in this line.

The for April formula would then be

=COUNTIFS([Inspection date]:[Inspection date], IFERROR(MONTH(@cell), 0) = 4, Status:Status, <>"Not Submitted")

But you need to make sure that every row has a text entry in the 'Status' column; everything will be counted as long as it doesn't say 'Not Submitted', even 'red', 'green', or 'blue' but not 14.

Or, if you wanted to only have entries in the 'Status' column for 'Non Submitted' and leave the other rows empty you could use:

=COUNTIF([Inspection date]:[Inspection date], IFERROR(MONTH(@cell), 0) = 4) - COUNTIFS([Inspection date]:[Inspection date], IFERROR(MONTH(@cell), 0) = 4, Status:Status, "Not Submitted")