| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum single or duplicate rows with multiple criteria
I am in construction and deal with expense reports from field employees. We
are trying to automate all to ease data entry of multiple line items containg the same data. Worksheet 1 is the field data entry sheet. Worksheet 2 would be the Print sheet that fills in and calculates from sheet 1. Trouble is the finding the formula to fill in the "Summary" box on sheet 2 by looking at all single or multiple cells that contain the same data on sheet 1. Because there are multiple jobs and codes, I want it to find and sum a combination of 3 cells of text (job numbers and codes) and sum a different cell on the same row without having to list our entire job, phase & category library. -- Thanks for any help available.. Lisa B |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum single or duplicate rows with multiple criteria
Probably something like
=SUMIF(Sheet!$A2:$A20,"Job1",Sheet2!$AC$2:$C20) or maybe even =SUMPRODUCT(--(Sheet!$A2:$A20="Job1"),--(Sheet!$B2:$B20="Code1"),Sheet2!$AC$2:$C20) but we would need more info to be specific. Give an example of the date/results. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lisa B" wrote in message ... I am in construction and deal with expense reports from field employees. We are trying to automate all to ease data entry of multiple line items containg the same data. Worksheet 1 is the field data entry sheet. Worksheet 2 would be the Print sheet that fills in and calculates from sheet 1. Trouble is the finding the formula to fill in the "Summary" box on sheet 2 by looking at all single or multiple cells that contain the same data on sheet 1. Because there are multiple jobs and codes, I want it to find and sum a combination of 3 cells of text (job numbers and codes) and sum a different cell on the same row without having to list our entire job, phase & category library. -- Thanks for any help available.. Lisa B |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum single or duplicate rows with multiple criteria
I have tried that but it would involve importing my whole cost code library.
What I need is a formula that will grab duplicates and sum them. I went ahead and merged the 3 cells in a text format but still have the $$ in a separate cell. I know Excel can do it, I just don't know how to write the formula. -- Thanks for any help available.. Lisa B "Bob Phillips" wrote: Probably something like =SUMIF(Sheet!$A2:$A20,"Job1",Sheet2!$AC$2:$C20) or maybe even =SUMPRODUCT(--(Sheet!$A2:$A20="Job1"),--(Sheet!$B2:$B20="Code1"),Sheet2!$AC$2:$C20) but we would need more info to be specific. Give an example of the date/results. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lisa B" wrote in message ... I am in construction and deal with expense reports from field employees. We are trying to automate all to ease data entry of multiple line items containg the same data. Worksheet 1 is the field data entry sheet. Worksheet 2 would be the Print sheet that fills in and calculates from sheet 1. Trouble is the finding the formula to fill in the "Summary" box on sheet 2 by looking at all single or multiple cells that contain the same data on sheet 1. Because there are multiple jobs and codes, I want it to find and sum a combination of 3 cells of text (job numbers and codes) and sum a different cell on the same row without having to list our entire job, phase & category library. -- Thanks for any help available.. Lisa B |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum single or duplicate rows with multiple criteria
As I said ... but we would need more info to be specific. Give an example of
the date/results. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lisa B" wrote in message ... I have tried that but it would involve importing my whole cost code library. What I need is a formula that will grab duplicates and sum them. I went ahead and merged the 3 cells in a text format but still have the $$ in a separate cell. I know Excel can do it, I just don't know how to write the formula. -- Thanks for any help available.. Lisa B "Bob Phillips" wrote: Probably something like =SUMIF(Sheet!$A2:$A20,"Job1",Sheet2!$AC$2:$C20) or maybe even =SUMPRODUCT(--(Sheet!$A2:$A20="Job1"),--(Sheet!$B2:$B20="Code1"),Sheet2!$AC$2:$C20) but we would need more info to be specific. Give an example of the date/results. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lisa B" wrote in message ... I am in construction and deal with expense reports from field employees. We are trying to automate all to ease data entry of multiple line items containg the same data. Worksheet 1 is the field data entry sheet. Worksheet 2 would be the Print sheet that fills in and calculates from sheet 1. Trouble is the finding the formula to fill in the "Summary" box on sheet 2 by looking at all single or multiple cells that contain the same data on sheet 1. Because there are multiple jobs and codes, I want it to find and sum a combination of 3 cells of text (job numbers and codes) and sum a different cell on the same row without having to list our entire job, phase & category library. -- Thanks for any help available.. Lisa B |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct with multiple criteria in single column | Excel Discussion (Misc queries) | |||
| Multiple rows from a single row | Excel Discussion (Misc queries) | |||
| Identify Duplicate Items Based On Multiple Criteria | Excel Worksheet Functions | |||
| Adding up data in multiple cells for a single criteria. | Excel Worksheet Functions | |||
| Sum Count of Single Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Linear Mode
