Using COUNTIF and SUMIF with dates

Hi everyone,

I need help understanding how I can use dates in COUNTIF and SUMIF formulas. I have all other parts of the formula working but as soon as I try and add a date condition at all it fails. What I need is something that makes it include all rows where the date in the column Date Closed is the current month.

My current formula looks like: =COUNTIFS({Bus Unit}, "QUARRIES VIC METRO", {RFI STATUS}, "CLOSED", {DATE CLOSED}, "MISSING BIT")

Can anyone help me with what goes in the missing bit?

Thanks

Find more posts tagged with

Formulas

Comments

Nic Larsen

This is a piece of a formula I've used to identify a specific month for conditional formulas:

[Date Range], IFERROR(MONTH(@cell), 0) = 1

Where the = 1 is looking for January. If the Cell is blank, it'll just move on and not toss you an error.

Without testing, your formula would look something like this:

=COUNTIFS({Bus Unit}, "QUARRIES VIC METRO", {RFI STATUS}, "CLOSED", {DATE CLOSED}, IFERROR(MONTH(@cell), 0 =1)

Paul Newcome

If you need a specific date, you would simply use a DATE(yyyy,mm,dd) in place of the MONTH function shown above. The same thing can be said for any other date related functions such as YEAR, WEEKNUMBER, YEARDAY, WEEKDAY, DAY, etc...

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.
Formula with range data
Hello, I'm looking to see if anyone knows a formula that would add owner income and co-owner income together to reflect in Joint Income column. I'm not sure how to do this with data in ranges. Thank you.