countif

  • Thread starter Thread starter EK
  • Start date Start date
E

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.
 
Try SUMPRODUCT:
=SUMPRODUCT(--(monthly!$E$12:$E$2000=11),--(monthly!$L$12:$L$2000<=0))

HTH,
Paul
 
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
 
ShaneDevenshire said:
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
Click to expand...
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
 
Back
Top