countif with two criteria one a column of dates?

L

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

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
 
G

Gary''s Student

To meet both criteria:

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

Lise

Fabulous - thankyou both :)
--

Lise


Dave Peterson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top