ExcelBanter - Sum calculation based on condition

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Sum calculation based on condition (https://www.excelbanter.com/excel-worksheet-functions/120295-sum-calculation-based-condition.html)

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

Sum calculation based on condition

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.

"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

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

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