countif

[EK]

Why does this not work:

Sheet A = named "monthly"

In column E number of mont is listed with 01 to 12. There can be various
amounts of rows filled with the different numbers of months

In column L the value can be 0 or less (f.x. -01)

In sheetb B = named "monthly summary" I want celle L4 to count rows when
criteria 11 is OK in monthly column E AND the criteria 0 or less is OK in
monthly column L

I have tried with this:
=countif($'monthly'.$E$12:$E$2000;"11");--($'monthly'.$L$12:$L$2000;"<=0")

I want to count from row 12 to 2000.

In my sheet the count seems to count the right amount of rows filled with 11
but does not regard wether the second criteria 0 or less is right or wrong.

Hope anybody can help me, even though my explanation might be a little
clumsy, due to my english.

 

[Bernard Liengme]

=SUMPRODUCT(--(Monthly!E1:E11=11),--(Monthly!L1:L11<=0))
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[PCLIVE]

Try SUMPRODUCT:
=SUMPRODUCT(--(monthly!$E$12:$E$2000=11),--(monthly!$L$12:$L$2000<=0))

HTH,
Paul

 

[ShaneDevenshire]

Hi,

COUNTIF does not support multiple criteria without great effort, try

=SUMPRODUCT(--($'monthly'.$E$12:$E$2000=11);--($'monthly'.$L$12:$L$2000;"<=0"))

I am also not sure about the "." period, in the english version this is "!"
and you may be able to use 11 instead of "11" but you will need to test with
your data.

If this helps, please click the Yes button

 

[EK]

Hi,

COUNTIF does not support multiple criteria without great effort, try

=SUMPRODUCT(--($'monthly'.$E$12:$E$2000=11);--($'monthly'.$L$12:$L$2000;"<=0"))

I am also not sure about the "." period, in the english version this is
"!"
and you may be able to use 11 instead of "11" but you will need to test
with
your data.

If this helps, please click the Yes button

Thanks to all of you. You brought me on the right track.
This one does the job for me:
=SUMPRODUCT(--(monthly!$E$12:$E$2000=11);--(monthly!$L$12:$L$2000<=0)+0)

Erik