Hello,
I need to do a dsum with a criteria of Isnumber(Check Number Column). I do
not know how to put a criteria in where I can return values only for those
rows where there is a number in another column.

Paid Check #
$100 1234
$200 Bounce
$300 4567
$400
$500 7777

I need a dsum to come back with $900 which are the 3 numeric looking Check
#'s. I can't figure out how to put the criteria in.

Thank you.

With the data in B5:C10...
=SUMIF(C6:C10,">0",B6:B10)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"jb"
wrote in message
Hello,
I need to do a dsum with a criteria of Isnumber(Check Number Column). I do
not know how to put a criteria in where I can return values only for those
rows where there is a number in another column.
Paid Check #
$100 1234
$200 Bounce
$300 4567
$400
$500 7777
I need a dsum to come back with $900 which are the 3 numeric looking Check
#'s. I can't figure out how to put the criteria in.
Thank you.

And just to add,

all the "d" functions require you to use a separte set of cells to hold the
criteria. You can't put the criteria in the dsum argument list itself

--
Regards,
Tom Ogilvy


"jb" wrote:

> Hello,
> I need to do a dsum with a criteria of Isnumber(Check Number Column). I do
> not know how to put a criteria in where I can return values only for those
> rows where there is a number in another column.
>
> Paid Check #
> $100 1234
> $200 Bounce
> $300 4567
> $400
> $500 7777
>
> I need a dsum to come back with $900 which are the 3 numeric looking Check
> #'s. I can't figure out how to put the criteria in.
>
> Thank you.
>
>
>

Perfect - thank you!

"Jim Cone" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> With the data in B5:C10...
> =SUMIF(C6:C10,">0",B6:B10)
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
>
> "jb"
> wrote in message
> Hello,
> I need to do a dsum with a criteria of Isnumber(Check Number Column). I
> do
> not know how to put a criteria in where I can return values only for those
> rows where there is a number in another column.
> Paid Check #
> $100 1234
> $200 Bounce
> $300 4567
> $400
> $500 7777
> I need a dsum to come back with $900 which are the 3 numeric looking Check
> #'s. I can't figure out how to put the criteria in.
> Thank you.
>
>

On a similar note, how do you do a compound criteria? That is, in the below
example, if I wanted to get all "Bounce" and all "Return", how do I specify
the Criteria? I tried "=Bounce OR Return", "=Bounce" OR "=Return" but
neither of those worked.

Also, trying to use not equal <>, how do you use AND or OR like <>Bounce And
<> Return?

Thanks for your help.

John

"Jim Cone" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> With the data in B5:C10...
> =SUMIF(C6:C10,">0",B6:B10)
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
>
> "jb"
> wrote in message
> Hello,
> I need to do a dsum with a criteria of Isnumber(Check Number Column). I
> do
> not know how to put a criteria in where I can return values only for those
> rows where there is a number in another column.
> Paid Check #
> $100 1234
> $200 Bounce
> $300 4567
> $400 Return
> $500 7777
> I need a dsum to come back with $900 which are the 3 numeric looking Check
> #'s. I can't figure out how to put the criteria in.
> Thank you.
>
>

For Bounce or Return

=SUM(SUMIF(B1:B200,{"bounce","return"},A1:A200))
or
=Sumif(B1:B200,"bounce",A1:A200)+Sumif(B1:B200,"return",A1:A200)

or
=sumproduct((B1:B200="Bounce")+(B1:B200="Return"),A1:A200)

for not equal to either, this does NOT work:
=SUM(SUMIF(B1:B200,{"<>bounce","<>return"},A1:A200))

This will work

=Sum(A1:A200)-SUM(SUMIF(B1:B200,{"bounce","return"},A1:A200))


--
Regards,
Tom Ogilvy


"jb" wrote:

> On a similar note, how do you do a compound criteria? That is, in the below
> example, if I wanted to get all "Bounce" and all "Return", how do I specify
> the Criteria? I tried "=Bounce OR Return", "=Bounce" OR "=Return" but
> neither of those worked.
>
> Also, trying to use not equal <>, how do you use AND or OR like <>Bounce And
> <> Return?
>
> Thanks for your help.
>
> John
>
> "Jim Cone" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > With the data in B5:C10...
> > =SUMIF(C6:C10,">0",B6:B10)
> > --
> > Jim Cone
> > San Francisco, USA
> > http://www.realezsites.com/bus/primitivesoftware
> > (Excel Add-ins / Excel Programming)
> >
> >
> > "jb"
> > wrote in message
> > Hello,
> > I need to do a dsum with a criteria of Isnumber(Check Number Column). I
> > do
> > not know how to put a criteria in where I can return values only for those
> > rows where there is a number in another column.
> > Paid Check #
> > $100 1234
> > $200 Bounce
> > $300 4567
> > $400 Return
> > $500 7777
> > I need a dsum to come back with $900 which are the 3 numeric looking Check
> > #'s. I can't figure out how to put the criteria in.
> > Thank you.
> >
> >
>
>
>

=SUM(B6:B10)-SUMIF(C6:C10,">0",B6:B10)
This just sums all numbers in column B then subtracts.
Jim Cone


"jb"
wrote in message
On a similar note, how do you do a compound criteria? That is, in the below
example, if I wanted to get all "Bounce" and all "Return", how do I specify
the Criteria? I tried "=Bounce OR Return", "=Bounce" OR "=Return" but
neither of those worked.

Also, trying to use not equal <>, how do you use AND or OR like <>Bounce And
<> Return?
Thanks for your help.
John



"Jim Cone"
wrote in message >
> With the data in B5:C10...
> =SUMIF(C6:C10,">0",B6:B10)
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)

>
>
> "jb"
> wrote in message
> Hello,
> I need to do a dsum with a criteria of Isnumber(Check Number Column). I
> do
> not know how to put a criteria in where I can return values only for those
> rows where there is a number in another column.
> Paid Check #
> $100 1234
> $200 Bounce
> $300 4567
> $400 Return
> $500 7777
> I need a dsum to come back with $900 which are the 3 numeric looking Check
> #'s. I can't figure out how to put the criteria in.
> Thank you.
>
>

Great. This makes a lot of sense.

John

"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:B2546DDC-E6C2-4A54-A691-(E-Mail Removed)...
> For Bounce or Return
>
> =SUM(SUMIF(B1:B200,{"bounce","return"},A1:A200))
> or
> =Sumif(B1:B200,"bounce",A1:A200)+Sumif(B1:B200,"return",A1:A200)
>
> or
> =sumproduct((B1:B200="Bounce")+(B1:B200="Return"),A1:A200)
>
> for not equal to either, this does NOT work:
> =SUM(SUMIF(B1:B200,{"<>bounce","<>return"},A1:A200))
>
> This will work
>
> =Sum(A1:A200)-SUM(SUMIF(B1:B200,{"bounce","return"},A1:A200))
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "jb" wrote:
>
>> On a similar note, how do you do a compound criteria? That is, in the
>> below
>> example, if I wanted to get all "Bounce" and all "Return", how do I
>> specify
>> the Criteria? I tried "=Bounce OR Return", "=Bounce" OR "=Return" but
>> neither of those worked.
>>
>> Also, trying to use not equal <>, how do you use AND or OR like <>Bounce
>> And
>> <> Return?
>>
>> Thanks for your help.
>>
>> John
>>
>> "Jim Cone" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >
>> > With the data in B5:C10...
>> > =SUMIF(C6:C10,">0",B6:B10)
>> > --
>> > Jim Cone
>> > San Francisco, USA
>> > http://www.realezsites.com/bus/primitivesoftware
>> > (Excel Add-ins / Excel Programming)
>> >
>> >
>> > "jb"
>> > wrote in message
>> > Hello,
>> > I need to do a dsum with a criteria of Isnumber(Check Number Column).
>> > I
>> > do
>> > not know how to put a criteria in where I can return values only for
>> > those
>> > rows where there is a number in another column.
>> > Paid Check #
>> > $100 1234
>> > $200 Bounce
>> > $300 4567
>> > $400 Return
>> > $500 7777
>> > I need a dsum to come back with $900 which are the 3 numeric looking
>> > Check
>> > #'s. I can't figure out how to put the criteria in.
>> > Thank you.
>> >
>> >
>>
>>
>>

Hmm, didn't think of that. Thank you.

John
"Jim Cone" <(E-Mail Removed)> wrote in message
news:ulA$(E-Mail Removed)...
>
> =SUM(B6:B10)-SUMIF(C6:C10,">0",B6:B10)
> This just sums all numbers in column B then subtracts.
> Jim Cone
>
>
> "jb"
> wrote in message
> On a similar note, how do you do a compound criteria? That is, in the
> below
> example, if I wanted to get all "Bounce" and all "Return", how do I
> specify
> the Criteria? I tried "=Bounce OR Return", "=Bounce" OR "=Return" but
> neither of those worked.
>
> Also, trying to use not equal <>, how do you use AND or OR like <>Bounce
> And
> <> Return?
> Thanks for your help.
> John
>
>
>
> "Jim Cone"
> wrote in message >
>> With the data in B5:C10...
>> =SUMIF(C6:C10,">0",B6:B10)
>> --
>> Jim Cone
>> San Francisco, USA
>> http://www.realezsites.com/bus/primitivesoftware
>> (Excel Add-ins / Excel Programming)
>
>>
>>
>> "jb"
>> wrote in message
>> Hello,
>> I need to do a dsum with a criteria of Isnumber(Check Number Column). I
>> do
>> not know how to put a criteria in where I can return values only for
>> those
>> rows where there is a number in another column.
>> Paid Check #
>> $100 1234
>> $200 Bounce
>> $300 4567
>> $400 Return
>> $500 7777
>> I need a dsum to come back with $900 which are the 3 numeric looking
>> Check
>> #'s. I can't figure out how to put the criteria in.
>> Thank you.
>>
>>
>
>

Last one, I promise! I haven't used Excel in quite a while and didn't
realize how many functions have been added that I am not aware of (been
using Access quite a bit) nor how to use effectively.

Without having to add a column to a spreadsheet, I need to count the number
of rows that match criteria based on three columns. That is, If column A is
blank AND column B=0 AND column C=0, this would count as 1 valid row,
otherwise 0 valid row. It looks like I could do this somehow with IF() and
I'd like to use one of the functions you guys have been referencing (sumIF,
countIF, etc.). I do not know how to use compound criteria. I thought I
could do something like =IF(A="" AND B=0 AND C=0, 1, 0), but that doesn't
work. I see there is an AND function: AND(A="",B=0,C=0) or something like
that. Very confused.

Thanks for any help!



"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:B2546DDC-E6C2-4A54-A691-(E-Mail Removed)...
> For Bounce or Return
>
> =SUM(SUMIF(B1:B200,{"bounce","return"},A1:A200))
> or
> =Sumif(B1:B200,"bounce",A1:A200)+Sumif(B1:B200,"return",A1:A200)
>
> or
> =sumproduct((B1:B200="Bounce")+(B1:B200="Return"),A1:A200)
>
> for not equal to either, this does NOT work:
> =SUM(SUMIF(B1:B200,{"<>bounce","<>return"},A1:A200))
>
> This will work
>
> =Sum(A1:A200)-SUM(SUMIF(B1:B200,{"bounce","return"},A1:A200))
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "jb" wrote:
>
>> On a similar note, how do you do a compound criteria? That is, in the
>> below
>> example, if I wanted to get all "Bounce" and all "Return", how do I
>> specify
>> the Criteria? I tried "=Bounce OR Return", "=Bounce" OR "=Return" but
>> neither of those worked.
>>
>> Also, trying to use not equal <>, how do you use AND or OR like <>Bounce
>> And
>> <> Return?
>>
>> Thanks for your help.
>>
>> John
>>
>> "Jim Cone" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >
>> > With the data in B5:C10...
>> > =SUMIF(C6:C10,">0",B6:B10)
>> > --
>> > Jim Cone
>> > San Francisco, USA
>> > http://www.realezsites.com/bus/primitivesoftware
>> > (Excel Add-ins / Excel Programming)
>> >
>> >
>> > "jb"
>> > wrote in message
>> > Hello,
>> > I need to do a dsum with a criteria of Isnumber(Check Number Column).
>> > I
>> > do
>> > not know how to put a criteria in where I can return values only for
>> > those
>> > rows where there is a number in another column.
>> > Paid Check #
>> > $100 1234
>> > $200 Bounce
>> > $300 4567
>> > $400 Return
>> > $500 7777
>> > I need a dsum to come back with $900 which are the 3 numeric looking
>> > Check
>> > #'s. I can't figure out how to put the criteria in.
>> > Thank you.
>> >
>> >
>>
>>
>>