| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sort by row instead of by column? | Excel Worksheet Functions | |||
| How to countif involving another column? | Excel Worksheet Functions | |||
| How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions | |||
| Copying the contents of a column into a chart | Excel Worksheet Functions | |||
| How Do I created a nested sumif - if this column has this and thi. | Excel Worksheet Functions | |||
Linear Mode
