Summing by categories
Hello,
I would like to summarise my dive times (columan A) by depth (column B). E.g. summed dive times for depths 0-9.9m, 10-19.9m, 20-29.9m, 30-39.9m, 40-49.9m, 50m-. I cannot find similar syntax in the Function Help section. Thank you, Tamsen |
Summing by categories
=SUMPRODUCT(--(B1:B10<10), A1:A10)
=SUMPRODUCT(--(B1:B10=10), --(B1:B10<20), A1:A10) =SUMPRODUCT(--(B1:B10=20), --(B1:B10<30), A1:A10) =SUMPRODUCT(--(B1:B10=30), --(B1:B10<40), A1:A10) =SUMPRODUCT(--(B1:B10=40), --(B1:B10<50), A1:A10) =SUMPRODUCT(--(B1:B10=50), A1:A10) "Tamsen" wrote: Hello, I would like to summarise my dive times (columan A) by depth (column B). E.g. summed dive times for depths 0-9.9m, 10-19.9m, 20-29.9m, 30-39.9m, 40-49.9m, 50m-. I cannot find similar syntax in the Function Help section. Thank you, Tamsen |
Summing by categories
Assuming that you have Time and Depth as headers in A1:B1 and data from
row 2 downwards... In C1 enter: Category In C2 enter & copy down: =LOOKUP(B2,{0,10,20,30,40,50},{"0-9.9m","10-19.9m","20-29.9m","30-39.9m","40-49.9m","=50m"}) Now select the range in A:C inclusing the headers and run: Data|PivotTables... which would allow you to create any summary type you want by category: Total, average, max, min, etc. all at once. Tamsen wrote: Hello, I would like to summarise my dive times (columan A) by depth (column B). E.g. summed dive times for depths 0-9.9m, 10-19.9m, 20-29.9m, 30-39.9m, 40-49.9m, 50m-. I cannot find similar syntax in the Function Help section. Thank you, Tamsen |
All times are GMT +1. The time now is 04:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com