If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
countif with two criteria one a column of dates?
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 |
#2
|
|||
|
|||
countif with two criteria one a column of dates?
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 |
#3
|
|||
|
|||
countif with two criteria one a column of dates?
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 |
#4
|
|||
|
|||
countif with two criteria one a column of dates?
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 . |
Thread Tools | |
Display Modes | |
|
|