• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIFS and Dates

C

chloec

Member
Hello. I have a scenario where I have 12 cells with dates in them:


Cell A1 Has =Today() and then the remaining 11 months are created like this -

=EDATE(A1,-1),=EDATE(A2,-1),=EDATE(A3,-1)...and so on. (Each cell should have the current month, and then the previous 11 months).


Then I want to use a COUNTIFS statement to count the number of times the month and year combo (only) shows up in a range where dates are entered like this: 11/09/2012, 11/10/2012, 11/01/2009... (I have other criteria that I look up, hence the Countifs).


My goal is to in cells B1,B2, B3 (and so on) evaluate the month AND year in in cells A1, A2, A3... and look at the range of dates and count how many numbers appear in that month/year.


Can you help me formulate the expression for B1, B2, B3, B4...etc. please!? In the example above - Cell A1 (11/20/2012) would have a count (answer) of 2 (11/09/12 and 11/10/2012) in cell B1. Thanks again!
 
Hi Chloe,

You could try something like the following in B1

=SUMPRODUCT(--(TEXT(DateRange, "yymm")=TEXT(A1,"yymm")))


Copy the formula to B2, B3, etc.


Cheers,

Sajan.
 
Hi Sajan,

How do I incorporate this into a countifs statement? As I do have other data to filter as well. Would it be something like this:


=countifs(SUMPRODUCT(--(TEXT(DateRange, "yymm")=TEXT(A1,"yymm"))>1,Name,"Ben", Number,"))


Thanks again!
 
Hi Chloe,

In this case, the SUMPRODUCT function is providing the count for the data that matches the criteria. AS such, you would not need to use the COUNTIFS function.


You can extend the "count" to include additional criteria, such as in the example below:

=SUMPRODUCT(((TEXT(DateRange, "yymm")=TEXT(A1,"yymm")))*(XYZRange="ValueToCheck")*(SomeOtherRange="AnotherValue"))


(The different ranges all need to have the same number of cells. For example, if DateRange has 10 rows, then XYZRange should also have 10 rows.)


If this example if not clear, please provide some more info regarding the additional criteria that you are looking to include.


Cheers,

Sajan.
 
This appears to be working fantastically! Thank you! Will post additional questions if needed. Thanks Sajan!
 
You must log in or register to reply here.
Back
Top