Using more than one criteria in Countif 1

[ADE6]

Hi

I am using the Countif function below to find the number of times that a particular month name appears in the column AH.

=COUNTIF(AH2:AH299,"APRIL")

I have another column AJ that contains positive and negative values that relate to each month shown in column AH.

I would like to write a formula that counts the number of a particular month(APRIL) that has negative values and one that counts positive values

For example

=COUNTIF(AH2:AH299,"APRIL") AJ2:AJ299,>0
=COUNTIF(AH2:AH299,"APRIL") AJ2:AJ299,<0

The Countif only seems to allow one criteria,how can i get round this?

Thanks for the ideas

Ade

 

[ADE6]

SOLVED


Try SumProduct

Less than zero
=SUMPRODUCT((AH2:AH299="April")*(AJ2:AJ299<=0))
Greater Than Zero
=SUMPRODUCT((AH2:AH299="April")*(AJ2:AJ299>0))

 

[GlennUK]

Hi Ade,

you can use the SUMPRODUCT function with some logical tests like this :

=SUMPRODUCT((AH2:AH299="APRIL")*(AJ2:AJ299>0))

By the way, why aren't you counting those that are exactly zero? Just curious.

Cheers, Glenn.

 

[GlennUK]

I see you've included zero now.

Cheers, Glenn.