Hi All,
I have a worksheet where I need to match the work items from Region A to Region B. If the work items match , then I need the formula to automatically populate the corresponding data in the next 3 columns as similar to Region A. I have tried searching the forum and but I could not work out the formula. I am not sure if the formula is Vlookup or INDEX/Match which both I am not well verse. I have attached a sample sheet. Thank you for your assistance.
Re: Multiple Condition/Criteria Sumif
Krishna,
I used the formula but it returnd 0 for all the staff. When you stated that i need to convert the staff id to true numbers, do I convert the staff id on the data sheet ? If I past on empty cell,do I need to modify the formula to read the staff id from another range? Sorry, I am quite new to sumproduct formula.
Hi all,
I am trying to create a multiple sumif based on my data. I am tracking errors made by staff according to their staff id, by the steps in their work and by the specific error type made. I am trying to summarize from the main data by using the sumif formula in this forum when I searched but it doesn't seems to work. Appreciate your assistance. I have attached a sample file where the formula should be at cell D5 on the Summary tab. Thanks.
Re: Formula To Calculate Same Numbers As One In Multiple Cells
Bob,
Thank you for your explanation.I have a better picture now what the formula means. The reason is I wanted to understand how the formula works as to learn and modify myself for any other use and understanding how the formula works is definitely the better learning process. Thanks for the link too!
Kumar
Re: Formula To Calculate Same Numbers As One In Multiple Cells
Bob,
Thanks ! It works. I would really appreciate if you could guide me what the formula actually means.For example does the sign<> and & has any significance?Thanks again.
Hi All,
I have a long list or work items that I track on a log on daily basis.Some work items are repeated twice or trice to input a certain comment.Therefore, when I try to calculate the total number of work items for the day using counta formula, if results in an inaccurate total since certain work items are repeated twice or trice. The work items consist of a 8 digit number.Example 8876098.
Is there a formula to calculate the total numner of work items in the data range and at the same time the formula should calculate repeated work items as only one ? Thanks.
Re: Create Staggered Chart
Hi Roughneck,
I ma not sure how to that as it might not be accurately reflecting a bell curve or standard deviation. I was wondering if excel is able to that since the list of staff is long. Is there a way ? Thanks.
Hi All,
I need assistance to figure out how I can create a staggered chart. I have a list of 48 staff with their quality scores in % for the month. Is there a way for Excel to sort this data in such a way that I can create a chart that is similar to Bell curve or a standard deviation curve ? This can be in a line or column format.Thanks.
Re: Sum Based on Criteria
Hi Dave,
My apologies. I will follow your instructions. Thank you again for all your assistance.
Kumar
Re: Sumproduct
Will and Norie,
Thank you for your suggestions.
I have tried Will's formula and it works. However, how do I modify this formula to take into account the dates as well ?I did try to generate a Pivot Table but some of the data is not correct compared to the original data especially the total error points.
Will, you mentioned that you were able to generate exactly the same PT s my table in the attachment, can I have look how you did that ? Thanks.
Hi All,
I need assistance to create a formula that calculates the number of work items reviewed and the total error points given to a particular employee for a week. I have approximately 45 employee and therefore I would need to create a formula to make it easier. The raw data though has the info for all the 45 staff. Is there a way I can use a formula to populate this data?
I think SUMPRODUCT will work but I am not well versed with this formula. I have attached a simplified version where I would need the formula in the second sheet. Appreciate if someone could assist me . Thanks.
Re: Display Correct Data in Pivot Table
Hi Norie,
Thank you! It works.I definitely will need to practice more. Thank you again!
Re: Display Correct Data in Pivot Table
Hi Norie,
What I would like Pivot Table to display is to choose any single employee by their employee ID, and display them by date,points, # of errors and error details. I also would like Pivot Table to sum the total points by date.
For example if I choose employee #1, I should be able to view that on each date, what was the sum of points for the whole day, the # of errors and the error details.(Each error is given a certain points i.e 0.25,0.50 or 1.0).
Please let me know if you would like more information and thanks in advance.
Hi All,
I am totally new to Pivot Table and I am trying to make my list of data to be correctly displayed in Pivot Table.I need help to figure out how to display the following from my data:
1. Employee ID
2. By Date
3. By Points
4. By # of Errors
5. By Error Details
I am not sure if this is possible by Pivot Table. I tried by trial and error but I cant get all of the above to be in the Pivot Table.Furthermore, I am unable to get Pivot Table to SUM the total error points for a particular date. Is there a way ? Thank you for your assistance.
Re: SUMIF on Filtered Data
Thanks Dave and Fing Fang. I will try this solutions first and I will revert back back it this works. I am still learning on pivot tables as well, so I would need to try this out. Thanks guys !
Hi All,
I need assistance to create a formula that combines SUMIF and SUBTOTAL. I have created a SUMIF function for a long list of data for approximately 45 staff based on a type of errors.What I would like to do is use the filter by staff id. For example, when I use the filter to choose John, the SUMIF function does not calculate only for John but it still shows for the entire staff.
Is there any way I could combine SUMIF and SUBTOTAL so that when I choose a certain staff from that long list, it will calculate accordingly.I have attached a simplified list of the spreadheet. What I need is when I filter by staff ID, the summary for error type and summary for errors by step to change automotically.Appreciate your help. Thanks.
Hi All,
I need help on time formula.I need the right formula to calculate the difference between time a staff started to work on an item and the time they completed the item. I also need to calculate how much time left for the balance of the day and the average time needed to complete the balance of the gtarget item. Example is the target for the day is 22 items. If the staff started to work and completed the first item at 8.15pm ( Her shift is 8pm to 5am), then how much time is left and how much is the average time needed to com plete the balance of the 21 items for the day.I also need the formula to adjust automatically with the allocated time.Example is 8 hours on normal days and 9 hours when there is one hour overtime.
I tried several methods but could not arrive at the right solution. I have attached the spreadsheet. The columns in red is where I need the formula.
Appreciate if anyone could teach me how to input the correct formula. Thanks.
Re: Sumproduct
Pangolin,
My apologies. When I tries replicating the form ula in the original spreadsheet, it shows #value!.Is there any criteria for this formula ? The original spreadsheet has in between blank spaces as well.Thanks.
Re: Sumproduct
Natalie/Pangolin,
Thanks for your reply.It works.I will try to learn from your solutions for other formulas that I would need to build in my work. Thanks guys.
Hi All,
I need help to expand my SUMPRODUCT to calculate a numerical value. I have set the formula to calculate how many medical leave, training and annual leave taken by my staff. Now, I need to expand the formula to calculate how many items that was completed on region1,region2 and region 3 as well as working hours. I tried adjusting the formula but it did not work. Appreciate your help. I have attached the simplified version of the file. The actual is as long as more than 10000 entries. Therefore , having a formula will greatly help.
