Formula or VBA help
 

I was wondering if there's a formula or code that could give the correct
result to the following:

I want to be able to add the corresponding amount from column C whenever
column A ends with the word Sum. In my example below the answer would be
5000.00. I thought about using the right function and then the sumif
function but I'm hoping there's an easier way.

Column A Column C
04-400-0527. Sum 1000.00
04-400-0528. Sum 1000.00
04-400-0529. 1000.00
04-400-0530. Sum 1000.00
04-400-0531 1000.00
04-400-0534. Sum 1000.00
04-400-0544. Sum 1000.00

Formula or VBA help
 

try
=sumproduct((right(a2:a22,3)="sum")*c2:c22)

--
Don Guillett
SalesAid Software

"Gilbert" wrote in message
...
I was wondering if there's a formula or code that could give the correct
result to the following:

I want to be able to add the corresponding amount from column C whenever
column A ends with the word Sum. In my example below the answer would be
5000.00. I thought about using the right function and then the sumif
function but I'm hoping there's an easier way.

Column A Column C
04-400-0527. Sum 1000.00
04-400-0528. Sum 1000.00
04-400-0529. 1000.00
04-400-0530. Sum 1000.00
04-400-0531 1000.00
04-400-0534. Sum 1000.00
04-400-0544. Sum 1000.00

Formula or VBA help
 

Gilbert,

You can do this quite easily using 'if' and 'right' functions.

In cell 'D1' enter the formula:
=IF(RIGHT(A1,3)="Sum",C1,0)

and then drag it down the rest of the column

This will put the value of in column C into your cells if the phrase 'Sum'
is present, all you then need to do is an autosum on column 'D' to get the
sum of the values.

HTH

Neil
www.nwarwick.co.uk
"Gilbert" wrote:

I was wondering if there's a formula or code that could give the correct
result to the following:

I want to be able to add the corresponding amount from column C whenever
column A ends with the word Sum. In my example below the answer would be
5000.00. I thought about using the right function and then the sumif
function but I'm hoping there's an easier way.

Column A Column C
04-400-0527. Sum 1000.00
04-400-0528. Sum 1000.00
04-400-0529. 1000.00
04-400-0530. Sum 1000.00
04-400-0531 1000.00
04-400-0534. Sum 1000.00
04-400-0544. Sum 1000.00

Formula or VBA help
 

Thanks a lot to both of you. The sumproduct is pretty nifty. Could you
please explain it?

"Don Guillett" wrote:

try
=sumproduct((right(a2:a22,3)="sum")*c2:c22)

--
Don Guillett
SalesAid Software

"Gilbert" wrote in message
...
I was wondering if there's a formula or code that could give the correct
result to the following:

I want to be able to add the corresponding amount from column C whenever
column A ends with the word Sum. In my example below the answer would be
5000.00. I thought about using the right function and then the sumif
function but I'm hoping there's an easier way.

Column A Column C
04-400-0527. Sum 1000.00
04-400-0528. Sum 1000.00
04-400-0529. 1000.00
04-400-0530. Sum 1000.00
04-400-0531 1000.00
04-400-0534. Sum 1000.00
04-400-0544. Sum 1000.00