Countif 2 ranges error

[SquirrelGuirrel]

HI there,
I am trying to count the number of times 2 criteria are met. The loan month must be Jan and the year must be 2002. If both criteria are not meant I would like a zero value returned. I am using the following formula and getting a VALUE error
=COUNTIF(G2:G59,"JAN")*AND(H2:H59,"2002"),"0")

Do the Date and month need to be formatted as text?

I searched the old posts but couldn't find any countif with an else scenario. Thanks for your help

 

[Aladin Akyurek]

On 2002-04-24 09:49, SquirrelGuirrel wrote:
HI there,
I am trying to count the number of times 2 criteria are met. The loan month must be Jan and the year must be 2002. If both criteria are not meant I would like a zero value returned. I am using the following formula and getting a VALUE error
=COUNTIF(G2:G59,"JAN")*AND(H2:H59,"2002"),"0")

Do the Date and month need to be formatted as text?

I searched the old posts but couldn't find any countif with an else scenario. Thanks for your help

One way:

=SUMPRODUCT((G2:G59,"JAN")*(H2:H59=2002))

 

[Jay Petrulis]

Hi,

Use SUMPRODUCT
=SUMPRODUCT((G2:G59="JAN")*(H2:H59=2002))

How are the dates formatted? If they are actual dates try,

=SUMPRODUCT(MONTH(G2:G59)=1)*(YEAR(H2:H59)=2002))

Untested, so I may have messed up the parentheses. In any event, this is the route to take.

Bye,
Jay

 

[SquirrelGuirrel]

Thank you very much