COUNTIF help

[srivkin78]

Colum H has values of OPEN and CLOSED
Colum AK has ranges of numbers .

I want to show the total number of colum J's that are under 50k only if colum h = OPEN

I tried this

=SUM(COUNTIF(AK12:AK6000,"<50000"), COUNTIF(H12:H6000,"=OPEN"))

Any ideas?

 

[Smitty]

Welcome to the Board!

You can use SUMPRODUCT:

=SUMPRODUCT((H1:H6000="OPEN")*(AK1:AK6000<50000))

Or if you have Excel 2007+ you can use COUNTIFS.

HTH,

 

[Aladin Akyurek]

Colum H has values of OPEN and CLOSED
Colum AK has ranges of numbers .

I want to show the total number of colum J's that are under 50k only if colum h = OPEN

I tried this

=SUM(COUNTIF(AK12:AK6000,"<50000"), COUNTIF(H12:H6000,"=OPEN"))

Any ideas?

=SUMPRODUCT(--(AK12:AK6000 < 50000),--(H12:H60000 = "OPEN"))

On Excel 2007 or later:

=COUNTIFS(AK12:AK6000,"<50000",H12:H60000,"OPEN")

If you have A2 house 50000 and B2 OPEN, then:

=SUMPRODUCT(--(AK12:AK6000 < E2),--(H12:H60000 = F2))

=COUNTIFS(AK12:AK6000,"<"&E2,H12:H60000,F2)

 

[srivkin78]

"=COUNTIFS(AK12:AK6000,"<50000",H12:H60000,"OPEN")

Thanks! that worked perfect with some tweaking!

 

[Aladin Akyurek]

"=COUNTIFS(AK12:AK6000,"<50000",H12:H60000,"OPEN")

Thanks! that worked perfect with some tweaking!

Great. Thanks for providing us feedback.