COUNTIF with two colunms

[Guest]

hi , again please someone tell me about follow formula.
i want to count if c/A has car and c/B has# >10 & <40
--A--- --B--
Car 11
Book 20
Appel 40
Car 30

thanks in advance.
tufail

 

[Ardus Petus]

=SUMPRODUCT((A:A="car")*(B:B>10)*(B:B<40))

HTH

 

[Guest]

thank you very much for quick reply, but sorry to say problame not solved
yet, following error is comingout.

#NUM! =SUMPRODUCT((A:A="A")*(B:B>10)*(B:B<40))
CAR 11
BOOK 15
APPEL 20
CAR 25

 

[Guest]

sorry to say this error is coming out, pls help me pleaseeeeeeee
=SUMPRODUCT((A:A="CAR")*(B:B>10)*(B:B<40)) this error cameout => #NUM!

 

[Guest]

with sumproduct you must specify row range, such as a1:a100 instead of just a
column such as a:a. empty rows do not hurt (other than speed) so specifiy a
range thatis ample.

 

[daddylonglegs]

You can't use whole column references with SUMPRODUCT, switch to
something like

=SUMPRODUCT((A1:A100="car")*(B1:B100>10)*(B1:B00<40))

 

[Ardus Petus]

My fault (untested code)
=SUMPRODUCT((A1:A999="CAR")*(B1:B999>10)*(B1:B999<40))

Sorry,

 

[Bob Phillips]

SP cannot use whole columns, you must specify the range, and all ranges must
be the same size.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)