multiple criteria date range

[Pat318]

I'm trying to countif in 2 columns 1has "O" or "MV". The other column has
date ranges.
So if dates 1/1/1985 -12/31/1985 "O" count how many. And do the same for
"MV" within the same date range. Here is the formula im using. Not sure if
I'm on the right track. It 's not working....
=COUNT(IF((G2:G1238="O")*(H2:H1238<12/31/1985)*(H2:H1238>1/1/1985),H2:H1238))

 

[Mike H]

Hi,

Try it like this

=SUMPRODUCT((G2:G1238="O")*(H2:H1238>=DATE(1985,1,1))*(H2:H1238<=DATE(1985,12,31)))

To make it more 'user friendly' put your lookup values in a cell

=SUMPRODUCT((G2:G1238=A1)*(H2:H1238>=A2)*(H2:H1238<=A3))

Where

A1= o
a2= 1/1/1985
a3= 31/12/1985

That way it's easy to alter the lookup parameters

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Pat318]

Thanks Alot Mike U R a life saver.
Pat318

Hi,

Try it like this

=SUMPRODUCT((G2:G1238="O")*(H2:H1238>=DATE(1985,1,1))*(H2:H1238<=DATE(1985,12,31)))

To make it more 'user friendly' put your lookup values in a cell

=SUMPRODUCT((G2:G1238=A1)*(H2:H1238>=A2)*(H2:H1238<=A3))

Where

A1= o
a2= 1/1/1985
a3= 31/12/1985

That way it's easy to alter the lookup parameters

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Bernard Liengme]

Not COUNTIF but SUMPRODUCT
And let's be careful how we refer to dates
=SUMPRODUCT(--(G2:G1238="O"), --(H2:H1238<DATE(1985,12,31)), --(H2:H1238>DATE(1985,1,1)))
will count how many records have the O and dates between those specified

To include the first and last date
=SUMPRODUCT(--(G2:G1238="O"), --(H2:H1238<=DATE(1985,12,31)), --(H2:H1238>=DATE(1985,1,1))
)

If you want O or MV
=SUMPRODUCT(--((G2:G1238="O")+(G2:G1238="MV")), --(H2:H1238<=DATE(1985,12,31)),
--(H2:H1238>=DATE(1985,1,1)) )

The double negation for the first term is not needed
=SUMPRODUCT(((G2:G1238="O")+(G2:G1238="MV")), --(H2:H1238<=DATE(1985,12,31)),
--(H2:H1238>=DATE(1985,1,1)) )


More info:
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct

best wishes

 

[Mike H]

Glad I could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.