Thanks:  0
Likes:  0

# Thread: Countif 2 ranges error

1. 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

2. 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))

3. 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

4. Thank you very much

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•