Sum calculation based on condition

[mniccole]

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

 

[Don Guillett]

Is there a reason the built in function SUMIF will not work? Look in the NON
vba help index.

 

[mniccole]

Is there a reason the built in function SUMIF will not work? Look in the NON
vba help index.

 

[mniccole]

I have about 6 conditions I need to test for.
I couldn't remember if I could use sum if else
thanks

 

[Guest]

You could use SUMPRODUCT.

Give us some information about your data and the conditions you want to test
for.

Dave

 

[mniccole]

You could use SUMPRODUCT.

Give us some information about your data and the conditions you want to test
for.

Dave

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..

 

[Bernard Liengme]

=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/assistance/2002/articles/xlconPT101.aspx