Summing by categories

[Guest]

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

 

[Guest]

=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)

 

[Aladin Akyurek]

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.