sumif involving another column
 

I'm stuck again. This time I need a formula to add column D by grouped
values in column A.

I don't know how to word my quandry, so I'll give an example...

The answer, using data below, is 400, because the cost for each trip number
is added only once even though it appears many times.

A B C D
Trip Covered Visited Cost
tr-24 Yes Factory 100
tr-24 Yes Office 100
tr-24 Yes Warehouse 100
tr-50 Office
tr-50 Factory
tr-16 Yes HQ 300
tr-16 Yes Office 300
tr-16 Yes Plant 300
tr-16 Yes Warehouse 300

Would this be another sumproduct array formula. I tried, but kept getting
the wrong answer. Could someone help me again. I thank you.

Excel 2000

I think I may have gotten it.

=SUMPRODUCT((a2:a10=a2:a10)*d2:d10)

The answer is correct. Does it look right to you?

Thanks.

"Bruce Norris" wrote in message
. ..
I'm stuck again. This time I need a formula to add column D by grouped
values in column A.

I don't know how to word my quandry, so I'll give an example...

The answer, using data below, is 400, because the cost for each trip
number is added only once even though it appears many times.

A B C D
Trip Covered Visited Cost
tr-24 Yes Factory 100
tr-24 Yes Office 100
tr-24 Yes Warehouse 100
tr-50 Office
tr-50 Factory
tr-16 Yes HQ 300
tr-16 Yes Office 300
tr-16 Yes Plant 300
tr-16 Yes Warehouse 300

Would this be another sumproduct array formula. I tried, but kept getting
the wrong answer. Could someone help me again. I thank you.

Excel 2000

If your data table starts in A1 and the data begins in row 2:

Try putting this formula in Cell E1:
=SUM(1/COUNTIF($A$2:$A$10,$A$2:$A$10)*$D$2:$D$10)

(Note: Commit that array formula by pressing [Ctrl]+[Shifr]+[Enter]

Regards,
Ron

No, I was wrong. The answer is incorrect. So, I still don't have the right
formula.


"Bruce Norris" wrote in message
.. .
I think I may have gotten it.

=SUMPRODUCT((a2:a10=a2:a10)*d2:d10)

The answer is correct. Does it look right to you?

Thanks.

"Bruce Norris" wrote in message
. ..
I'm stuck again. This time I need a formula to add column D by grouped
values in column A.

I don't know how to word my quandry, so I'll give an example...

The answer, using data below, is 400, because the cost for each trip
number is added only once even though it appears many times.

A B C D
Trip Covered Visited Cost
tr-24 Yes Factory 100
tr-24 Yes Office 100
tr-24 Yes Warehouse 100
tr-50 Office
tr-50 Factory
tr-16 Yes HQ 300
tr-16 Yes Office 300
tr-16 Yes Plant 300
tr-16 Yes Warehouse 300

Would this be another sumproduct array formula. I tried, but kept getting
the wrong answer. Could someone help me again. I thank you.

Excel 2000

That's it! Thanks, Ron!!


"Ron Coderre" wrote in message
...
If your data table starts in A1 and the data begins in row 2:

Try putting this formula in Cell E1:
=SUM(1/COUNTIF($A$2:$A$10,$A$2:$A$10)*$D$2:$D$10)

(Note: Commit that array formula by pressing [Ctrl]+[Shifr]+[Enter]

Regards,
Ron