COUNTIF with two colunms
 

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

COUNTIF with two colunms
 

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

HTH
--
AP

"Tufail" a écrit dans le message de news:
...
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

COUNTIF with two colunms
 

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:B10)*(B:B<40))
CAR 11
BOOK 15
APPEL 20
CAR 25


"Ardus Petus" wrote:

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

HTH
--
AP

"Tufail" a écrit dans le message de news:
...
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

COUNTIF with two colunms
 

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


"Tufail" wrote:

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

COUNTIF with two colunms
 

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.

"Tufail" wrote:

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


"Tufail" wrote:

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

COUNTIF with two colunms
 

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

=SUMPRODUCT((A1:A100="car")*(B1:B10010)*(B1:B00<4 0))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=546445

COUNTIF with two colunms
 

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

Sorry,
--
AP

"duane" a écrit dans le message de news:
...
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.

"Tufail" wrote:

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


"Tufail" wrote:

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

COUNTIF with two colunms
 

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)

"Tufail" wrote in message
...
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:B10)*(B:B<40))
CAR 11
BOOK 15
APPEL 20
CAR 25


"Ardus Petus" wrote:

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

HTH
--
AP

"Tufail" a écrit dans le message de
news:
...
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