| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum calculation based on condition
HI:
I need to be able to loop though column a & if a condition is true, I need to caculate a sum in based on the value in column m.. I need to be able to display the count of the active cell & the sum of column m & loop though..I have numerous values I have to set this for... I'm trying this & it's not working.. Any help would be greatly appreciated... If ActiveCell.Value = "SPECIALTY CARE" Then SpecCount = x + 1 ' sum = Range("M2:M2", Range("M2").End(xlDown)).Calculate sum = Range("M2:M2000").Formula = "=sum(M2:M2)" I think I've confused myself.... THANK YOU |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum calculation based on condition
Is there a reason the built in function SUMIF will not work? Look in the NON
vba help index. -- Don Guillett SalesAid Software "mniccole" wrote in message ups.com... HI: I need to be able to loop though column a & if a condition is true, I need to caculate a sum in based on the value in column m.. I need to be able to display the count of the active cell & the sum of column m & loop though..I have numerous values I have to set this for... I'm trying this & it's not working.. Any help would be greatly appreciated... If ActiveCell.Value = "SPECIALTY CARE" Then SpecCount = x + 1 ' sum = Range("M2:M2", Range("M2").End(xlDown)).Calculate sum = Range("M2:M2000").Formula = "=sum(M2:M2)" I think I've confused myself.... THANK YOU |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum calculation based on condition
Don Guillett wrote: Is there a reason the built in function SUMIF will not work? Look in the NON vba help index. -- Don Guillett SalesAid Software "mniccole" wrote in message ups.com... HI: I need to be able to loop though column a & if a condition is true, I need to caculate a sum in based on the value in column m.. I need to be able to display the count of the active cell & the sum of column m & loop though..I have numerous values I have to set this for... I'm trying this & it's not working.. Any help would be greatly appreciated... If ActiveCell.Value = "SPECIALTY CARE" Then SpecCount = x + 1 ' sum = Range("M2:M2", Range("M2").End(xlDown)).Calculate sum = Range("M2:M2000").Formula = "=sum(M2:M2)" I think I've confused myself.... THANK YOU |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum calculation based on condition
I have about 6 conditions I need to test for.
I couldn't remember if I could use sum if else thanks Don Guillett wrote: Is there a reason the built in function SUMIF will not work? Look in the NON vba help index. -- Don Guillett SalesAid Software "mniccole" wrote in message ups.com... HI: I need to be able to loop though column a & if a condition is true, I need to caculate a sum in based on the value in column m.. I need to be able to display the count of the active cell & the sum of column m & loop though..I have numerous values I have to set this for... I'm trying this & it's not working.. Any help would be greatly appreciated... If ActiveCell.Value = "SPECIALTY CARE" Then SpecCount = x + 1 ' sum = Range("M2:M2", Range("M2").End(xlDown)).Calculate sum = Range("M2:M2000").Formula = "=sum(M2:M2)" I think I've confused myself.... THANK YOU |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum calculation based on condition
Dave F wrote: You could use SUMPRODUCT. Give us some information about your data and the conditions you want to test for. Dave -- Brevity is the soul of wit. I wonder about brevity sometimes... What I have to do is take a existing excel worksheet (that I did not design) & test the value = (Rentals, specialty, IS, vendor, etc) located in column a If that condition is true, I need to take teh invoice from that specific dept & keep a running total... Example: Column A Column M VENDOR $189.00 There would be numerous of teh rows with different invoice prices. If ActiveCell.Value = "SPECIALTY" Then sum = Range("M2:M2").Formula = "=sum(M2:M2)" SpecCount = x + 1 Thanks Dave.. "mniccole" wrote: I have about 6 conditions I need to test for. I couldn't remember if I could use sum if else thanks Don Guillett wrote: Is there a reason the built in function SUMIF will not work? Look in the NON vba help index. -- Don Guillett SalesAid Software "mniccole" wrote in message ups.com... HI: I need to be able to loop though column a & if a condition is true, I need to caculate a sum in based on the value in column m.. I need to be able to display the count of the active cell & the sum of column m & loop though..I have numerous values I have to set this for... I'm trying this & it's not working.. Any help would be greatly appreciated... If ActiveCell.Value = "SPECIALTY CARE" Then SpecCount = x + 1 ' sum = Range("M2:M2", Range("M2").End(xlDown)).Calculate sum = Range("M2:M2000").Formula = "=sum(M2:M2)" I think I've confused myself.... THANK YOU |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum calculation based on condition
=SUMPRODUCT(--(A2:A2000="VENDOR"),M2:M2000)
Or with the test VENDOR in E1 =SUMPRODUCT(--(A2:A2000=E1),M2:M2000) Try this with a formula in a cell (not VBA) Alternatively have a go at making a Pivot Table. See Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx -------------------------------------------------------------------------------- best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "mniccole" wrote in message ups.com... Dave F wrote: You could use SUMPRODUCT. Give us some information about your data and the conditions you want to test for. Dave -- Brevity is the soul of wit. I wonder about brevity sometimes... What I have to do is take a existing excel worksheet (that I did not design) & test the value = (Rentals, specialty, IS, vendor, etc) located in column a If that condition is true, I need to take teh invoice from that specific dept & keep a running total... Example: Column A Column M VENDOR $189.00 There would be numerous of teh rows with different invoice prices. If ActiveCell.Value = "SPECIALTY" Then sum = Range("M2:M2").Formula = "=sum(M2:M2)" SpecCount = x + 1 Thanks Dave.. "mniccole" wrote: I have about 6 conditions I need to test for. I couldn't remember if I could use sum if else thanks Don Guillett wrote: Is there a reason the built in function SUMIF will not work? Look in the NON vba help index. -- Don Guillett SalesAid Software "mniccole" wrote in message ups.com... HI: I need to be able to loop though column a & if a condition is true, I need to caculate a sum in based on the value in column m.. I need to be able to display the count of the active cell & the sum of column m & loop though..I have numerous values I have to set this for... I'm trying this & it's not working.. Any help would be greatly appreciated... If ActiveCell.Value = "SPECIALTY CARE" Then SpecCount = x + 1 ' sum = Range("M2:M2", Range("M2").End(xlDown)).Calculate sum = Range("M2:M2000").Formula = "=sum(M2:M2)" I think I've confused myself.... THANK YOU |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy data from 1 worksheet to another based on a condition | Excel Worksheet Functions | |||
| Fill a cell based on a condition being met | Excel Worksheet Functions | |||
| Change row color based on condition of celss | Excel Worksheet Functions | |||
| Counting Across Multiple Ranges, Based on Condition | Excel Worksheet Functions | |||
| make a cell empty based on condition | Charts and Charting in Excel | |||
Linear Mode
