countif with two criteria one a column of dates?

[Lise]

I have two columns and am entering the following formula
=COUNTIF($B$2:$B$436,"**-AU-****")+COUNTIF($A$2:$A$436,"**/**/2005")

The wildcards in the B column are used to show that I only want to count the
cells in that column that have AU in the middle.

I then want to break it down futher by counting how many times 2005 appears
in the corresponding cell of column A

This is not working - where am I going wrong please?

 

[Dave Peterson]

If that -AU- has to be in the 3rd to 6th character:

=sumproduct(--(mid($b$2:$b$436,3,4)="-AU-"),
--(year($a$2:$a$436)=2005))

If it can be anywhere:

=sumproduct(--(IsNumber(search("-AU-",$B$2:$B$436))),
--(Year($A$2:$A$436)=2005))

Use Find if that -AU- is different from -au- (case matters).

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

 

[Gary''s Student]

To meet both criteria:

=SUMPRODUCT((MID(A2:A436,4,2)="AU")*(YEAR(B2:B436)=2005))

 

[Lise]

Fabulous - thankyou both
--

Lise

If that -AU- has to be in the 3rd to 6th character:

=sumproduct(--(mid($b$2:$b$436,3,4)="-AU-"),
--(year($a$2:$a$436)=2005))

If it can be anywhere:

=sumproduct(--(IsNumber(search("-AU-",$B$2:$B$436))),
--(Year($A$2:$A$436)=2005))

Use Find if that -AU- is different from -au- (case matters).

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