Advanced CountIF Formula Help 1

[socomfort]

Hello all:

I have detail data in a sheet where the first column contains a transaction date. The date format is "MMM-YY". So several records dated 1/1/2007, 1/31/2007, 1/19/2007 will all show as "Jan-07".

What I want to do is count the number of instances that "Jan-07" exists on this sheet based upon a match to the date "1/1/2007" on another sheet (cell B7) in the same format.

I attempted the following, but it does not work:

{=COUNT(IF(AND(('Sheet2'!$A:$A>=Sheet1!B$7),('Sheet2'!$A:$A<=(EOMONTH(Sheet1!B$7,0)))),'Sheet2'!$A:$A,))}

I am attempting to say, count all the instances of Jan-07 in column A of the detail sheet where the dates are greater than or equal to 1/1/2007 and less than or equal to 1/31/2007. B7 is the cell containing the date '1/1/2007'.

Any advice you all can give me is much appreciated.

thanks,

Ben

 

[Fenrirshowl]

I'd simplify things: use 2 countifs, one for all the dates less than 1 Feb and then deduct all those less than 1 Jan.

 

[xlbo]

Definitely - you can'r use an AND in a COUNTIF - you would need to use SUMPRODUCT but the easiest way is as Fenrirshowl has described

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[LaurieOZ]

Hi socomfort

Try the following

=SUMPRODUCT(0+(MONTH(Sheet1!$A:$A)=MONTH(Sheet2!B7)))

Regards

Laurie

 

[SkipVought]

[tt]
=sumproduct((sheet2!$A$1:$A$9999>=B7)*(sheet2!$A$1:$A$9999<DATE(Year(b7),month(b7)+1,0)))
[/tt]


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[socomfort]

Thank you all so very much for your help. Skip, I tried your code and it worked great. Hats off to you!

LaurieOZ, Fenrirshowl, and xlbo..thank you all for pointing me in the right direction. What a terrific site.

sincerely,

Ben