Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
Perfect - thank you!
"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 $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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
For Bounce or Return
=SUM(SUMIF(B1:B200,{"bounce","return"},A1:A200)) or =Sumif(B1:B200,"bounce",A1:A200)+Sumif(B1:B200,"re turn",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" 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
=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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
Great. This makes a lot of sense.
John "Tom Ogilvy" wrote in message ... For Bounce or Return =SUM(SUMIF(B1:B200,{"bounce","return"},A1:A200)) or =Sumif(B1:B200,"bounce",A1:A200)+Sumif(B1:B200,"re turn",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" 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
Hmm, didn't think of that. Thank you.
John "Jim Cone" wrote in message ... =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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
dsum with IsNumber()
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" wrote in message ... For Bounce or Return =SUM(SUMIF(B1:B200,{"bounce","return"},A1:A200)) or =Sumif(B1:B200,"bounce",A1:A200)+Sumif(B1:B200,"re turn",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" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISNUMBER | Excel Worksheet Functions | |||
ISNumber | Excel Programming | |||
isnumber() | Excel Programming | |||
ISNumber VBA | Excel Programming | |||
ISNUMBER | Excel Programming |