COUNTIF

[pep]

Hi!

Is it really so that you cannot use special * or ? characters in COUNTIF
function?

I would like to use them because I have hundreds of different dates
(e.g.01.01.2007 13:30) in different rows in column A and I would like to
count e.g. how many row has January as month and year as 2007.

I tried something like this:

=COUNTIF(A:A;"*01.2007*")

but it cannot find any.

Or is there some easier way to do this?
-Paul

 

[Don Guillett]

=sumproduct((year(a2:a22)=2007)*(month(a2:a22)=1))

 

[Earl Kiosterud]

Paul,

A pivot table can be made to group on months and years, and can give you counts. If you'll
want counts of all month-year combinations, a pivot table will give them all to you as it
finds them in your data.

I donät know if the COUNTIF function can be coddled, coerced or finessed into giving zou
such a breakdown, though someone might come forth with one. We usuallz escalate to the more
geekz SUMPRODUCT function for this, since Excel wonät give us a readzßmade easzßtoßuse
function for such a multipleßcriteria summarz function.

Well, hell. Outlook Express has started changing characters from the kezboard again. I
have to reboot to get it back on track.

Anzwaz, trz this.

=SUMPRODUCT((YEAR(B2:B65000)=2007)*(MONTH(B2:B65000)=1))

Gotta reboot. See zou later.

 

[Ragdyer]

You might try this formula, where you enter the month you're looking to
count into B1 (jan, feb, mar, ... etc.),
And the year into B2 (2005, 2006, ... etc.)

=SUMPRODUCT((TEXT(A1:A100,"mmm")=B1)*(--TEXT(A1:A100,"yyyy")=B2)*(A1:A100<>"
"))

 

[Ragdyer]

You can drop the last argument.
I was using that in a different approach:

=SUMPRODUCT((TEXT(A1:A100,"mmm")=B1)*(--TEXT(A1:A100,"yyyy")=B2))

 

[Dave Peterson]

One more:
=sumproduct(--(text(a1:a10,"yyyymm")="200701"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[pep]

Thanks for all.

Sumproduct works well!

-Paul