I want to create a formula so that I can keep track of how many feet get out of a drill bit. The data will be arranged in this way:

table of individual drillings

I want to show results of the total footage drilled by:

table of drilling totals

Any ideas on formulas?

  • Can you clarify for us what those numbers mean? Are they all in a single row? what is the row/column structure like? – Barry Rosenberg Oct 28 '13 at 21:05

Assuming that "Bit ID" is A1, 15685 in A2, etc...

=sumif(A2:A11, 15685, B2:B11)

Where A2:A11 specifies the various bit ids, B2:B11 specifies the numbers you want to add up, and 15682 is the specific bit id you're looking for. That would probably actually be a reference to another cell, so you could re-use this formula for all the different bit sizes.


It sounds like what you are after is a pivot table:

  • Enter your data as shown in your question
  • Select the entire range of data
  • On the insert menu, choose pivot table
  • Choose somewhere to put it in the dialog which pops up
  • In the field list section which appears on the right, drag the Bit ID into the Row Labels box at the bottom.
  • Drag the Ft drilled into the Values box at the bottom
  • It should default to sum, but if not, click on the ft drilled item in the values box, choose Value Field Settings and change the summarize method to Sum.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.