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?
--
Thanks as always

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 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

To meet both criteria:

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

--
Gary''s Student - gsnu201002


"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?
--
Thanks as always

Lise

Fabulous - thankyou both :-)
--

Lise


"Dave Peterson" wrote:

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
.