search and sum

A

avensrose

I have a spreadsheet with many employees and their expenses. I am looking
for a formula that finds the employees and adds up their expenses.
 
P

pdberger

I don't know how your data is laid out, but here's one way

A B
1 Abe $1
2 Betty $2
3 Abe $3
4 Betty $3
5 Sam $2
6
7 Abe =SUMIF($A$1:$A$5,A7,$b$1:$b$5)
8 Betty copy
9 Sam down

HTH
 
J

Jim Thomlinson

There are a couple of possible solution depending on your specific needs. If
there is only one criteria the you can use a sumif formula something like
this...
=SumIf(A1:A10, "Jim", B1:B10)
To find instances of Jim in the range A1:A10 and sum the corresponding
values in B1:B10...

If you have more than one criterian then you would want a sumproduct
formula... More like this but with extra criteria...
=Sumproduct(--(A1:A10="Jim"), B1:B10)
 
A

avensrose

That helps alot, but I have more than one column to find and add. I can get
it to work with one column, but when I add more columns I get an error.
 
J

Jim Thomlinson

We need the exact layout of your data in order to help... What data is in
which columns...
 
A

avensrose

Initials / Code / Supplies / Catering / Per Diem / Trans / Hotel / Tips
11-Oct BAP / SNA / / / $36.00 / /
/ $5.00
25-Sep GWB / LAS / / / $36.00 / /
/
11-Oct JAD / IAD / / / $36.00 / /
/ $10.00
11-Oct JLL / SNA / / / $36.00 / /
/ $5.00


Of course my spreedsheet is much larger, but as an example, I want to know
how much is goes to BAP. I normally have more than one row for each set of
initials and the amounts are normally in more than one column. I remember
doing something like this before, but it has been about 10 years.

Thank you for letting me pick your brains. I have been trying to solve this
for 2 hours
 
J

Jim Thomlinson

The best way is probably to add a column at the end of the data for the
total. It will add up the amounts for each rows Supplies, Catering, Per Diem,
.... You can then just use the sum if to get the total by initials...

=sumif(B2:B100, "BAP", J2:J100)
Where the row total is in column J
 
A

avensrose

THANKS!!!!!

Jim Thomlinson said:
The best way is probably to add a column at the end of the data for the
total. It will add up the amounts for each rows Supplies, Catering, Per Diem,
... You can then just use the sum if to get the total by initials...

=sumif(B2:B100, "BAP", J2:J100)
Where the row total is in column J
Click to expand...
 
Top