Excel using a =countif formula

I am trying to count cells on a separate sheet based on Type in this case its "Hardware" and date range. In this case between 10/7/2012 and 10/24/2012. Looking at my spreadsheet I have two items that match this criteria. However, when I run the formula the counts are 0

=COUNTIFS(AirCheck!C:C, "Hardware", AirCheck!G:G,"<=10/17/2012", AirCheck!G:G, ">=10/24/2012")

i think you need a helper column with =AND(DATE(2012,10,7)<=B3,B3<=DATE(2012,10,24)) this will give you a series of true/false then you can use formula =COUNTIFS(A:A,"Hardware",C:C,"True") to count "Hardware" and "True" to get what you want, countif/countifs cannot use >,< or = as a criteria

obviously change the formulas above to include your sheet names and appropriate columns

also dates are stored as serial numbers 1 being 01/01/1900 and are formatted to show the us date format (or other)

I guess i'm not quite sure if I follow. Would a copy of the spreadsheet help? the counts should be on the counts tab

good to know, my way is still better because you don't have to update the formula if your criteria changes, instead you can update the input cells.

also, question, does excel know that that the string ie. 12/12/2012 is a date or will it assume it is a text string?

Originally Posted by scott.s.fower

...my way is still better...

First, I think you have misinterpreted the problem that krisarmstrong is trying to solve. Please see post #7 and the attachment there. The example in the OP, and solution you provided, use the same range of begin/end dates for every column. But in looking at the worksheet it is clear that each column represents data for a different week, and so each column in "Counts" should be looking at a different range of dates. There is not much point in having exactly the same counts repeated in every column. This is a gap in the original description, although krisarmstrong has not returned to the thread to comment on that.

Second, even if the interpretation were correct, and if we use your method of putting the begin and end dates into special cells, it is still possible to solve this without using a helper column. I would disagree that a helper column is better if you can use straightforward formulas that work without them. For example, in B7 you could use this formula without a helper column:

=COUNTIFS(AirCheck!$C:$C, $A7, AirCheck!$G:$G,">=" & H$1, AirCheck!$G:$G, "<" & J$1)

Third, even if the interpretation were correct, and you use a helper column, there is an error in your solution. Your formula in column H of sheet AirCheck produces only 1 TRUE result, and it's for 12/06/12. Clearly that date is not in the range 10/7/12-10/24/12 and all the other dates that are in that range are flagged FALSE. Your formula in H2 is

=AND(Counts!H1<=G2,G2<=Counts!J1)

It works fine in H2 but when copied down to all rows, the row references for the H and J cells in Counts also increment. The incorrect formula in H27, for example, is

=AND(Counts!H26<=G27,G27<=Counts!J26)

The correct formula for H2 is

=AND(Counts!$H$1<=G2,G2<=Counts!$J$1)

and can be copied down.

does excel know that that the string ie. 12/12/2012 is a date or will it assume it is a text string?

Excel does implied coercion. If it is being compared to a date it will be interpreted as a date, and if it is compared to a string it will be interpreted as a string.

First your formula on the Hardware line of the Counts tab (ie B7) is =COUNTIFS(AirCheck!B:B, "Configuration", AirCheck!F:F,"<=10/17/12", AirCheck!F:F, ">=10/24/12"), i'm guessing that's wrong
second the column in the formula is F:F and it should be $G:$G so it is locked to the Insert Date column which is Column G on the AirCheck Tab so when you copy the formula to another cell it still references the Insert Date column

so on your AirCheck tab, insert a column next to Insert Date and put in the =AND(DATE(2012,10,7)<=G2,G2<=DATE(2012,10,24)) formula, this will give you a true/false in the column

then on the Counts tab in (ie B7) enter the formula =COUNTIFS(Aircheck!$C:$C,"Hardware",AirCheck!$H:$H,"True") to get your count (PS there are none with "Hardware" and the given date range). Even better use the formula =COUNTIFS(Aircheck!$C:$C,$A7,AirCheck!$H:$H,"True")which is the "Hardware" row header in cell A7 of the Counts Tab

you could further refine it so the "Validate Date Rng" (the column I added on the AirCheck Tab) will be updated with input cells on the Counts tab so you can easily change your date range for future use

krisarmstrong, you have many other problems in this sheet as well.

The second condition includes "=" but should not, if you are trying to look for counts within a week. In your example, if 10/17 is the first day of a week, 10/24 is the first day of the next week.

Your formulas include a string that is supposed to be the same as the description in column A, except it doesn't always match. For example, row 7 is for Hardware but your formula says "Configuration". You can just refer to the column A value instead of include the actual string. Attached is a cleaned up version. I did this separately from the above fix so as not to confuse the issue with two changes at the same time.

You have a similar problem with dates. You are using the same date range typed into the formulas in the entire sheet, but maybe you just want the week for the column you are in. I have also fixed that, assuming the dates in row 3 are the start dates for the week in that column.

You are also using relative references for the columns in AirCheck and copied those formulas, so you are looking in different columns for each week. The week of Oct 31 (column C) is the only one that has the correct formula.

I fixed all of these, see attached.