Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
Ok, I guess this got sticky because of how detail I'm trying to get it
to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
Try something like this:
=SUMPRODUCT(--(A1:A1000="Criteria1"),--(B1:B1000="Criteria2"),--(C1:C1000="Criteria3"),D1:D1000) HTH, Paul wrote in message ups.com... Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
What exactly are you trying to compute for each employee? Do want a summary
by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
Yes, I think you've grasped what it is I'm trying to achieve. A
summary is what I'm looking for. For example, if on January 1, Employee 1 purchased an airline ticket to visit a client. The amount was $600. Now on that same day, meeting with the client would mean charging a business lunch at $40 as well as a dinner at $70. I want to create a user defined sheet with a drop down list of those employees. For each employee name chosen, I wanted each type of transaction summarized. Such as, one cell will return the airfaire of $600. The next to return the total amount of food on that day of $110, i.e. the lunch and dinner. Is that asking for too much of Excel? What exactly are you trying to compute for each employee? Do want a summary by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
Thanks Paul,
But I'm not looking to count the amount of fells filled. I need a summary and/or the value to display. Try something like this: =SUMPRODUCT(--(A1:A1000="Criteria1"),--(B1:B1000="Criteria2"),--(C1:C1000="*Criteria3"),D1:D1000) HTH, Paul wrote in message ups.com... Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
I think you should try this. Unless I'm misunderstanding you, you want a sum
of the amounts in column D that match a specified date, a specified employee, and a specified transaction type. That is what this will do. This formula will match the criteria of column A, the date (in this example is 6/13/2007 which is 39246 when viewed in General format), column B, the employee name "Employee Bob", and column C, the type of transaction (in this case "Food"). Column D is then summed for the rows matching the three criteria. =SUMPRODUCT(--(A1:A1000=39246),--(B1:B1000="Employee Bob"),--(C1:C1000="Food"),D1:D1000) Ultimately the date could be in a cell on your sheet in which you would not need to use the numeric value...you would just reference the cell that will contain the date you want. Also, the employee name and possibly even the type of transaction can exist in a cell in which the formula can refer to without having to change the formula each time you want to change the criteria. Example, the date you want to match may be in A1 of the current sheet. The employee name may be in A2, and the desired transaction type may be in A3. Let's say your logged data is on sheet 1 =SUMPRODUCT(--(Sheet1!A1:A1000=A1),--(Sheet1!B1:B1000=A2),--(Sheet1!C1:C1000=A3),Sheet1!D1:D1000) HTH, Paul wrote in message oups.com... Yes, I think you've grasped what it is I'm trying to achieve. A summary is what I'm looking for. For example, if on January 1, Employee 1 purchased an airline ticket to visit a client. The amount was $600. Now on that same day, meeting with the client would mean charging a business lunch at $40 as well as a dinner at $70. I want to create a user defined sheet with a drop down list of those employees. For each employee name chosen, I wanted each type of transaction summarized. Such as, one cell will return the airfaire of $600. The next to return the total amount of food on that day of $110, i.e. the lunch and dinner. Is that asking for too much of Excel? What exactly are you trying to compute for each employee? Do want a summary by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
OK .... Paul's reply was basically correct:
=SUMPRODUCT(--(A1:A1000="Criteria1"),--(B1:B1000="Criteria2"),--(C1:C1000="Criteria3"),D1:D1000) For example Criteria1= Your employee Criteria2= Type of transaction Criteria3 = Date of transaction D1:D1000 = monetary values So you could have headings in row 1 column B onwards of your transacations ("Airfare","Lunch" ...). A2=Employee name A3=Date (?) If B1="Airfare" then in B2: =SUMPRODUCT(--($A$1:$A$1000=$A$2),--($B$1:$B$1000=B$1),--($C$1:$C$1000=$A$3),$D$1:$D$1000) will total for Employee (A2) for expense type (B1) on date (A3) Copy across for expense types. HTH " wrote: Yes, I think you've grasped what it is I'm trying to achieve. A summary is what I'm looking for. For example, if on January 1, Employee 1 purchased an airline ticket to visit a client. The amount was $600. Now on that same day, meeting with the client would mean charging a business lunch at $40 as well as a dinner at $70. I want to create a user defined sheet with a drop down list of those employees. For each employee name chosen, I wanted each type of transaction summarized. Such as, one cell will return the airfaire of $600. The next to return the total amount of food on that day of $110, i.e. the lunch and dinner. Is that asking for too much of Excel? What exactly are you trying to compute for each employee? Do want a summary by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
..... I got the criteria order wrong, but you get the idea! See Paul's more
complete reply. "Toppers" wrote: OK .... Paul's reply was basically correct: =SUMPRODUCT(--(A1:A1000="Criteria1"),--(B1:B1000="Criteria2"),--(C1:C1000="Criteria3"),D1:D1000) For example Criteria1= Your employee Criteria2= Type of transaction Criteria3 = Date of transaction D1:D1000 = monetary values So you could have headings in row 1 column B onwards of your transacations ("Airfare","Lunch" ...). A2=Employee name A3=Date (?) If B1="Airfare" then in B2: =SUMPRODUCT(--($A$1:$A$1000=$A$2),--($B$1:$B$1000=B$1),--($C$1:$C$1000=$A$3),$D$1:$D$1000) will total for Employee (A2) for expense type (B1) on date (A3) Copy across for expense types. HTH " wrote: Yes, I think you've grasped what it is I'm trying to achieve. A summary is what I'm looking for. For example, if on January 1, Employee 1 purchased an airline ticket to visit a client. The amount was $600. Now on that same day, meeting with the client would mean charging a business lunch at $40 as well as a dinner at $70. I want to create a user defined sheet with a drop down list of those employees. For each employee name chosen, I wanted each type of transaction summarized. Such as, one cell will return the airfaire of $600. The next to return the total amount of food on that day of $110, i.e. the lunch and dinner. Is that asking for too much of Excel? What exactly are you trying to compute for each employee? Do want a summary by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
Thank you the both of you, gentlemen.
And Paul, my mistake. In other spreadsheets I've created, I used the "sumproduct" to count text data. I didn't stop to think that it would take sum of numerical values, such as the charge amounts in this case. Again, thank you. OK .... Paul's reply was basically correct: =SUMPRODUCT(--(A1:A1000="Criteria1"),--(B1:B1000="Criteria2"),--(C1:C1000="*Criteria3"),D1:D1000) For example Criteria1= Your employee Criteria2= Type of transaction Criteria3 = Date of transaction D1:D1000 = monetary values So you could have headings in row 1 column B onwards of your transacations ("Airfare","Lunch" ...). A2=Employee name A3=Date (?) If B1="Airfare" then in B2: =SUMPRODUCT(--($A$1:$A$1000=$A$2),--($B$1:$B$1000=B$1),--($C$1:$C$1000=$A$3*),$D$1:$D$1000) will total for Employee (A2) for expense type (B1) on date (A3) Copy across for expense types. HTH " wrote: Yes, I think you've grasped what it is I'm trying to achieve. A summary is what I'm looking for. For example, if on January 1, Employee 1 purchased an airline ticket to visit a client. The amount was $600. Now on that same day, meeting with the client would mean charging a business lunch at $40 as well as a dinner at $70. I want to create a user defined sheet with a drop down list of those employees. For each employee name chosen, I wanted each type of transaction summarized. Such as, one cell will return the airfaire of $600. The next to return the total amount of food on that day of $110, i.e. the lunch and dinner. Is that asking for too much of Excel? What exactly are you trying to compute for each employee? Do want a summary by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
It's ok, Toppers. I was able to to figure it out, being that all I had
to do was adjust the respective cells to the ones in my actual spreadsheet. The scenario I gave you was just a sample of my actual spreadsheet. Not only that, they were just one of a few criterias, so it really doesn't matter what order they are in. Especially with the solution you gave me. Thanks again. .... I got the criteria order wrong, but you get the idea! See Paul's more complete reply. "Toppers" wrote: OK .... Paul's reply was basically correct: =SUMPRODUCT(--(A1:A1000="Criteria1"),--(B1:B1000="Criteria2"),--(C1:C1000="*Criteria3"),D1:D1000) For example Criteria1= Your employee Criteria2= Type of transaction Criteria3 = Date of transaction D1:D1000 = monetary values So you could have headings in row 1 column B onwards of your transacations ("Airfare","Lunch" ...). A2=Employee name A3=Date (?) If B1="Airfare" then in B2: =SUMPRODUCT(--($A$1:$A$1000=$A$2),--($B$1:$B$1000=B$1),--($C$1:$C$1000=$A$3*),$D$1:$D$1000) will total for Employee (A2) for expense type (B1) on date (A3) Copy across for expense types. HTH " wrote: Yes, I think you've grasped what it is I'm trying to achieve. A summary is what I'm looking for. For example, if on January 1, Employee 1 purchased an airline ticket to visit a client. The amount was $600. Now on that same day, meeting with the client would mean charging a business lunch at $40 as well as a dinner at $70. I want to create a user defined sheet with a drop down list of those employees. For each employee name chosen, I wanted each type of transaction summarized. Such as, one cell will return the airfaire of $600. The next to return the total amount of food on that day of $110, i.e. the lunch and dinner. Is that asking for too much of Excel? What exactly are you trying to compute for each employee? Do want a summary by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
You're welcome. Sumproduct is a great function.
wrote in message ups.com... Thank you the both of you, gentlemen. And Paul, my mistake. In other spreadsheets I've created, I used the "sumproduct" to count text data. I didn't stop to think that it would take sum of numerical values, such as the charge amounts in this case. Again, thank you. OK .... Paul's reply was basically correct: =SUMPRODUCT(--(A1:A1000="Criteria1"),--(B1:B1000="Criteria2"),--(C1:C1000="*Criteria3"),D1:D1000) For example Criteria1= Your employee Criteria2= Type of transaction Criteria3 = Date of transaction D1:D1000 = monetary values So you could have headings in row 1 column B onwards of your transacations ("Airfare","Lunch" ...). A2=Employee name A3=Date (?) If B1="Airfare" then in B2: =SUMPRODUCT(--($A$1:$A$1000=$A$2),--($B$1:$B$1000=B$1),--($C$1:$C$1000=$A$3*),$D$1:$D$1000) will total for Employee (A2) for expense type (B1) on date (A3) Copy across for expense types. HTH " wrote: Yes, I think you've grasped what it is I'm trying to achieve. A summary is what I'm looking for. For example, if on January 1, Employee 1 purchased an airline ticket to visit a client. The amount was $600. Now on that same day, meeting with the client would mean charging a business lunch at $40 as well as a dinner at $70. I want to create a user defined sheet with a drop down list of those employees. For each employee name chosen, I wanted each type of transaction summarized. Such as, one cell will return the airfaire of $600. The next to return the total amount of food on that day of $110, i.e. the lunch and dinner. Is that asking for too much of Excel? What exactly are you trying to compute for each employee? Do want a summary by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
Hi
This data layout is ideally suited for summarising with a Pivot Table. Mark your range of data. DataPivot TableNextFinish Drag name to the Page field area Drag Dates to the Row area Drag Transaction types to the Row area alongside the Dates Drag Charge amounts to the Data area. If you want, right click on DateGrouping and OutlineGroupChoose whatever period you want Days 7 or Days 15 or Month Select any employee name from the page dropdown and you have your report. -- Regards Roger Govier wrote in message oups.com... Yes, I think you've grasped what it is I'm trying to achieve. A summary is what I'm looking for. For example, if on January 1, Employee 1 purchased an airline ticket to visit a client. The amount was $600. Now on that same day, meeting with the client would mean charging a business lunch at $40 as well as a dinner at $70. I want to create a user defined sheet with a drop down list of those employees. For each employee name chosen, I wanted each type of transaction summarized. Such as, one cell will return the airfaire of $600. The next to return the total amount of food on that day of $110, i.e. the lunch and dinner. Is that asking for too much of Excel? What exactly are you trying to compute for each employee? Do want a summary by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many criterias...
Thanks Roger,
I did try the Pivot Table route. However, with the goal I had in mind, it didn't quite fit for what I was trying to do. Thanks for responding though. Hi This data layout is ideally suited for summarising with a Pivot Table. Mark your range of data. DataPivot TableNextFinish Drag name to the Page field area Drag Dates to the Row area Drag Transaction types to the Row area alongside the Dates Drag Charge amounts to the Data area. If you want, right click on DateGrouping and OutlineGroupChoose whatever period you want Days 7 or Days 15 or Month Select any employee name from the page dropdown and you have your report. -- Regards Roger Govier wrote in message oups.com... Yes, I think you've grasped what it is I'm trying to achieve. A summary is what I'm looking for. For example, if on January 1, Employee 1 purchased an airline ticket to visit a client. The amount was $600. Now on that same day, meeting with the client would mean charging a business lunch at $40 as well as a dinner at $70. I want to create a user defined sheet with a drop down list of those employees. For each employee name chosen, I wanted each type of transaction summarized. Such as, one cell will return the airfaire of $600. The next to return the total amount of food on that day of $110, i.e. the lunch and dinner. Is that asking for too much of Excel? What exactly are you trying to compute for each employee? Do want a summary by transaction types for each employee for a given time period? " wrote: Ok, I guess this got sticky because of how detail I'm trying to get it to be with the multiple criterias. The purpose of this sheet is to track business expenses amongst about 8 employees. Assume columns: (with up to a thousand rows of data) A will consist of Dates B will consist of Employee Names C will consist of Transaction Types (i.e. Business lunches, airfare, etc) D will consist of the Charge Amounts. My first obstacle is after meeting the 3 criterias, for a cell to return the value in column C. My 2nd obstacle is there are many duplicates. Now there are a lot of formulas to eliminate them. That's not the case this time. If row 2 matches all the values in row 1 respectively through columns A, B, C, and D, then either take the sum of the 2 transactions to show as one trasnaction type on that day by that employee, or return it in the next cell and I can just do a sumif function. Forgive me if i'm not explaining this with complete clarity, but I guess this is the best I can think to desribe it. If you have any solutions and/or strategies, please let me know.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Criterias | Excel Discussion (Misc queries) | |||
Sum If using 2 criterias | Excel Discussion (Misc queries) | |||
Sumif with two criterias | Excel Worksheet Functions | |||
2 criterias for countif - | Excel Worksheet Functions | |||
Countif, two criterias need help | Excel Worksheet Functions |