sumif involving another column

[Bruce Norris]

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

 

[Bruce Norris]

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.

 

[Guest]

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

 

[Bruce Norris]

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

 

[Bruce Norris]

That's it! Thanks, Ron!!