Totals on lookups

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Ernie Fenwick said:
I have a table of up to 35 mileages(numerical) in Col M
with a project code(numerical) alongside (can be to left
or right).
Some project codes are repeated several times. Other than
using the subtotal function which needs to be done
manually, can I create another table with a unique project
code with its total mileage automatically updated when the
original table has data appended.

Thanks in anticipation

Ernie Fenwick
Click to expand...

Something like
=SUMPRODUCT((N1:N100=project_code)*M1:M100)
You replace project_code with, well, whatever project code you're looking
for!
Alternatively, you can substitute a reference to a cell containing the
project code. (This is good for constructing a table, as you then list the
possible project codes in one column and copy the formula down from the
first cell in the column next to it.)
It doesn't have to be column N for the project code.
The formula copes with blanks, so the ranges (such as M1:M100 and N1:N100)
can be longer than you need - but they must be the same length as each
other.
 
I have a table of up to 35 mileages(numerical) in Col M
with a project code(numerical) alongside (can be to left
or right).
Some project codes are repeated several times. Other than
using the subtotal function which needs to be done
manually, can I create another table with a unique project
code with its total mileage automatically updated when the
original table has data appended.

Thanks in anticipation

Ernie Fenwick
 
Ernie,

Is this what you want

=SUMPRODUCT((L1:L10="A")+(N1:N10="A"),(M1:M10))

Simply change the ="A" for your particular codes


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top