formula

M

MMM

I am working with a word pad document that I imported into excel. I need a
formula that will total the inspectors money that was collected. There are
numerous stations and the rows will vary every day. One day there might only
be 50 rows, the next day may be 65 rows of data. I have copied part of the
spreadsheet for an example of what I am dealing with. I need a total amount
for 4122, 4124 and 4302.

STATION/INSPECTOR/AMOUNT /This is the formula i need to give me these amounts
---- ------ ------------
P-01 4122 1,188.25 1188.25
P-01 4124 869.75 869.75
---- ------ ------------
P-02 4302 9.5 95.25
P-02 4302 85.75 (blank)
 
S

SteveG

MMM,

Try,

=SUMIF(B2:B1000,4122,C2:C1000)

or

=SUMPRODUCT((A2:B1000=4122)*(C2:C1000))

Adjust the 4122 as needed for different inspectors.

HTH

Steve
 
M

MMM

Thank you, that helps me out, but I have one more question.
Is there a way to write that formula without having to put the inspector
number in it (i.e. 4122)? Those numbers will change every day and there is
no way to tell in advance what they will be.
 
S

SteveG

It may be easier to use a pivot table. Select your range of cell
including headers. Open the Pivot Table Wizard. Click Next. Clic
Next. Click Layout. Drag your header Inspector to the Row section o
the left. Drag your Amount header to the Data section. Change that t
Sum by double clicking on it and picking Sum from the option list. Clic
OK. Select the location for your Pivot Table and click Finish. You ca
then hide the fields with blanks and totals in them.

When you dump in the new data, make sure your Pivot Table range i
large enough to capture all of the data and then Refresh the pivo
table. You may just want to set the range once to a size that you wil
not exceed in the future. This will show any new Inspector ID's and th
amount totals once refreshed.

Does that help?

Stev
 
Top