Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and sum
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and sum
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 "avensrose" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and sum
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) -- HTH... Jim Thomlinson "avensrose" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and sum
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. "pdberger" wrote: 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 "avensrose" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and sum
We need the exact layout of your data in order to help... What data is in
which columns... -- HTH... Jim Thomlinson "avensrose" wrote: 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. "pdberger" wrote: 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 "avensrose" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and sum
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and sum
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 -- HTH... Jim Thomlinson "avensrose" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and sum
THANKS!!!!!
"Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "avensrose" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|