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 anywhe
=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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Lise wrote:
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