range of sumif

[b166er]

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.

[Bob Phillips]

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

--
HTH

Bob Phillips

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

"b166er" <[email protected]> wrote in
message news:[email protected]...
>
> 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=553244
>

[[email protected]]

PIVOT TABLE would be my solution of choice

failing that try DSUM

DSUM
See Also

Adds the numbers in a column of a list or database that match
conditions you specify.

Syntax

DSUM(database,field,criteria)

Database is the range of cells that makes up the list or database. A
database is a list of related data in which rows of related information
are records, and columns of data are fields. The first row of the list
contains labels for each column.

Field indicates which column is used in the function. Field can be
given as text with the column label enclosed between double quotation
marks, such as "Age" or "Yield," or as a number that represents the
position of the column within the list: 1 for the first column, 2 for
the second column, and so on.

Criteria is the range of cells that contains the conditions you
specify. You can use any range for the criteria argument, as long as it
includes at least one column label and at least one cell below the
column label for specifying a condition for the column.




b166er wrote:
> 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=553244

[CLR]

With names in column A, and amounts in column B, put this in C1 to total all
of column B except those amounts relative to John, bush, and kaven.

=SUM(B1:B10)-SUMIF(A1:A10,"john",B1:B10)-SUMIF(A1:A10,"bush",B1:B10)-SUMIF(A1:A10,"kaven",B1:B10)

Vaya con Dios,
Chuck, CABGx3



"b166er" wrote:

>
> 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=553244
>
>

[CLR]

Sorry, I mis-read the post.........should be this.

=SUMIF(A1:A10,"john",B1:B10)+SUMIF(A1:A10,"bush",B1:B10)+SUMIF(A1:A10,"kaven",B1:B10)

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

> With names in column A, and amounts in column B, put this in C1 to total all
> of column B except those amounts relative to John, bush, and kaven.
>
> =SUM(B1:B10)-SUMIF(A1:A10,"john",B1:B10)-SUMIF(A1:A10,"bush",B1:B10)-SUMIF(A1:A10,"kaven",B1:B10)
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "b166er" wrote:
>
> >
> > 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=553244
> >
> >

[RagDyeR]

Try this:

=SUM(SUMIF(A1:A50,{"John","Bush","Kaven"},B1:B50))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"b166er" <[email protected]> wrote in
message news:[email protected]...

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=553244