COUNTIFS with Text and Date

[NJS1982]

Hi all, wonder if anyone can help. I have two columns;

- E contains dates, in MM-YY format (i.e. 'Feb-19', 'Mar-18')
- F contains text ('Same' and 'Changed').

I want to create a formula that counts the number of instances 'Same' or 'Changed' appears in a month. So far I have this:

Looking at the number in March that are the same:
=COUNTIFS(E:E,"Mar-18",F:F,"Same")
and then to look at the number in March that have changed:
=COUNTIFS(E:E,"Mar-18",F:F,"Changed")

...it presents me a figure but it isn't right, so am I doing something incorrect?? Thanks!

 

[Joe4]

You are comparing dates to Text, and that won't work ("Mar-18" in your formula will be treated as text).
The formulas don't care what the format of the date field is, they will run off the underlying data, which is a full date.

So you would need to do something like this:
=COUNTIFS(E:E,">=" & DATE(2018,3,1),E:E,"<=" & DATE(2018,3,31),F:F,"Same")

 

[WassimN]

Hello NJS1982

Are the dates in Column E text or date. If it is dates then possibly think of comparing the integer that reprsents that date vs. a text prepresentation where an space can throw things off.

Try this in a new Column and then report back if you get the expected results.

 

[Weazel]

as long as the dates in column e are all valid dates the countifs should be able to interpret "Mar-18" as month and year without a problem.

its also possible that you may have extra spaces in column d that would cause the "same" / "changed" values not to be counted.

you could try checking your counts with something like...

=SUMPRODUCT((E2:E10="mar-18"+0)*(F2:F10="same"))

it won't be very efficient on the full column but you could check your math.

you might also consider throwing it into a pivot table and see what you get for counts

 

[Joe4]

=SUMPRODUCT((E2:E10="mar-18"+0)*(F2:F10="same"))

I don't think that will work.

The originally said:

- E contains dates, in MM-YY format (i.e. 'Feb-19', 'Mar-18')

So "Mar-18" represents the month of March and Year of 2018.
If it is really entered as a date, there is a day component stored, regardless of whether or not they are showing it.

This:

="mar-18"+0

Actually returns Month of "March", Day of "18", and the current year "2018".
So your formula would only return records were the date in column E is EXACTLY March 18, 2018.

I think they want to return all records where the date in column E falls anywhere in the month of March, hence you need to check the date range (March 1, 2018 - March 31, 2018).
If you knew that the days in the column E for all the entries used the exact same day, then you could use equals, but if that is not certain, then you will need to use the range.

 

[NJS1982]

Thanks all, really helpful.

The Mar-18 column is actually coming from another cell with a meeting date in;four meetings in a month and I just want to group them into months, hence the MM-YYYY format.

All I am trying to do is find out the number of times 'Changed' and 'Same' appear each month over a period of time...

 

[Weazel]

yeah, I see your point Joe

maybe something like...

=SUMPRODUCT((TEXT(E2:E10,"MMM-YY")="Mar-18")*(F2:F10=I1))

 

[Joe4]

yeah, I see your point Joe

maybe something like...

=SUMPRODUCT((TEXT(E2:E10,"MMM-YY")="Mar-18")*(F2:F10=I1))

Yep, that one seems to work.
That is the advantage to using SUMPRODUCT over COUNTIFS, you can run conversions on the data you are checking!
So using it, you can do one less criteria than using COUNTIFS.