sumif
 

hi members,

can i give criteria range for sumif funtion i tried but i couldn't
give criteria range.

if criteria range can not be given then how can we use or command to
give more than one arguments say i want sum in col B5 all the the sale
made by either of three salepersons(john,bush,kaven).
i dont mind if someone tell me some other function through which i
could solve that problem.


--
b166er
------------------------------------------------------------------------
b166er's Profile: http://www.excelforum.com/member.php...o&userid=34912
View this thread: http://www.excelforum.com/showthread...hreadid=553228

sumif
 

Are you after sometime like this.

=SUMIF($A$5:$A$8,"John",$B$5:$B$8)

This website may help

http://www.j-walk.com/ss/excel/tips/tip74.htm


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=553228

sumif
 

thanks for reply buy i want

A1 john 5555
A2 bush 20
A3 kaven 50
A4 linda 10
A5 jams 50

i just want to sum the amount of john bush and kaven in one cell there
are more than 5000 enteries keep in mind that thing as well.


--
b166er
------------------------------------------------------------------------
b166er's Profile: http://www.excelforum.com/member.php...o&userid=34912
View this thread: http://www.excelforum.com/showthread...hreadid=553228

sumif
 

=SUMIF($A$5:$A$8,"John",$B$5:$B$8)+SUMIF($A$5:$A$8 ,"bush",$B$5:$B$8)+
SUMIF($A$5:$A$8,"kaven",$B$5:$B$8)


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=553228

sumif
 

hi, why not use:

=SUMIF($A$5:$A$5005,"John",$B$5:$B$5005)+SUMIF($A$ 5:$A$5005,"bush",$B$5:$B$5005)+SUMIF($A$5:$A$5005, "kaven",$B$5:$B$5005)

hope this helps
regards from Brazil
Marcelo





"b166er" escreveu:


thanks for reply buy i want

A1 john 5555
A2 bush 20
A3 kaven 50
A4 linda 10
A5 jams 50

i just want to sum the amount of john bush and kaven in one cell there
are more than 5000 enteries keep in mind that thing as well.


--
b166er
------------------------------------------------------------------------
b166er's Profile: http://www.excelforum.com/member.php...o&userid=34912
View this thread: http://www.excelforum.com/showthread...hreadid=553228

sumif
 

or use an array

=SUM((IF((($A$5:$A$5005="john")+($A$5:$A$5005="bus h")+($A$5:$A$5005="Kaven")),1)*$B$5:$B$5005))

Use Ctrl +shift + enter to apply array


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=553228

sumif
 

=SUMPRODUCT(SUMIF(A1:A100,{"John","Bush","Kaven"}, C1:C100))

as shown in the other two groups that you posted in.

--
HTH

Bob Phillips

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

"b166er" wrote in
message ...

hi members,

can i give criteria range for sumif funtion i tried but i couldn't
give criteria range.

if criteria range can not be given then how can we use or command to
give more than one arguments say i want sum in col B5 all the the sale
made by either of three salepersons(john,bush,kaven).
i dont mind if someone tell me some other function through which i
could solve that problem.


--
b166er
------------------------------------------------------------------------
b166er's Profile:
http://www.excelforum.com/member.php...o&userid=34912
View this thread: http://www.excelforum.com/showthread...hreadid=553228

sumif
 

Just spotted that too Bob.

Neat solution by the way


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=553228