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)

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=561171

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.

"SteveG" wrote:


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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=561171

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

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

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=561171