countifs and date not working right.

[kztdat]

Hi all

I have an issue that seem very straight forwards so i hope one of you can help.

I'm trying to run a countifs function across two columns of data to only count what fits two criteria. The second column is a dates that is represented by "8/23/2013 11:02:00 AM".

My formula looks like the following (not working of course):

=COUNTIFS(E2:E636,"=C2",I2:I636,"=8/23/2013*")

What I experience is that when I change the second criteria to "8/23/2013 11:02:00 AM", the formula works fine. However it will not find a date "8/23/2013 11:05:00 AM". I really just want the second criteria to find all August 2013 items as I would like to tally each month of a specific year". Is there a way for me to change the date to a shorter date to help in my search.

My question is therefore how do i get the formula to work if I need only need the month and year?

I Hope you understand my issue and can help me.

Thanks in advance

[Jonmo1]

First, I'm a little confused on the 1st criteria "=C2"
That is literally looking for a text string of "C2"..NOT the value of the Cell C2..
Is that what you want?

anyway, given that you said this works as desired...
=COUNTIFS(E2:E636,"=C2",I2:I636,"8/23/2013 11:02:00 AM")

But you want to count ALL of Auguest 2013, then try

=COUNTIFS(E2:E636,"=C2",I2:I636,">=" & "8/1/2013"+0,I2:I636,"<" & "9/1/2013"+0)

[kztdat]

Thanks that was really helpful and worked great. Never thought of the using it with a range, forgot it can do that.

Two additional question:

1. What does the +0 do after the date? (For reference)

2. Is there a way to put ">=" & "8/1/2013"+0 into a cell and i point to it in the formula? It worked so well my boss wants me to add a third criteria and the third criteria has 12 choices, so updating 12 formulas for 3 groups and 12 months gets long when the only change are the criteria for date.

[Jonmo1]

The +0 converts a Text String ("8/1/2013") into a real numerical Date value.

To refer to a cell with the givend ates, do it like

">="&E1

Where E1 holds the date.

[kztdat]

Thanks works perfect. saved me a ton.

[Jonmo1]

Glad to help, thanks for the feedback.

[BaldEagle199]

I am having a similar problem, but the proposed solution is not working for me. My formula is =COUNTIF(Orders!E$3:E$1832,"="&Analysis!A2) where E and A are both dates. I verified they are dates by using =Month, which properly yields the number of the month (e.g. 11/21/2013 = 11). I have tried it with and without the "="&, and I get zero "0" as a result.

[Jonmo1]

Sounds like one of the dates (either in A2 or column E, or both) is a Date AND Time value..

Format the relevant cells as mm/dd/yyyy hh:mm:ss
What do you see?

[BaldEagle199]

Thanks for the quick reply. I do not see mm/dd/yyyy hh:mm:ss as a date format option in Excel 2013

[Jonmo1]

Choose custom, and type it by hand.

Or choose any Date format that includes the time..

[BaldEagle199]

I changed the format as prescribed and it is still not working.

[Jonmo1]

I didn't say that would fix it..
That was a troubleshooting step.

What do you see in those cells now, do they contain TIME as well as the date?

[BaldEagle199]

Yes, the cells are displaying time as well as date.

[Jonmo1]

Which ones, in Orders!E$3:E$1832 or Analysis!A2 or Both ?

[Jonmo1]

Try

=COUNTIFS(Orders!E$3:E$1832,">="&INT(Analysis!A2),Orders!E$3:E$1832,"<"&INT(Analysis!A2)+1)

[BaldEagle199]

That worked! Thanks so much for the assistance!