Posts by jag.seven

  • Explanation of SUMPRODUCT INDIRECT & SUMIF Formula

    Re: Explanation of SUMPRODUCT INDIRECT & SUMIF Formula


    Thank you Domenic and Wigi. Your response has been very helpful.


    Domenic, i downloaded Morefunc.xll but when I run the setup, it says "An instance of Excel is already running .Please close it and click on "Install". Is there any reason for this ? Thanks.

  • Explanation of SUMPRODUCT INDIRECT & SUMIF Formula

    Hi all,



    I have a set of data as attached where the productivity of each staff is recorded on a daily basis.However, the cell in which their name is located on each tab of the day of the month might not be the same. I am thankful that the forum actually provided me with the formula located in cell c15.
    1)However, I would appreciate if someone could translate what the formula means as I am not so sure what the formula represents.
    2)The reason is I would like to calculate how many training days (TR), Medical Leave(ML) and Emergency Leave(EL) for the month of January for each staff.I tried modifying the formula but it didnt work.
    3) Is it possible to use a vlookup function for this solution?


    The actual data is for the whole month of January with over 50 staff.I have the simplified version as attached. Thank you for your help.


    Kumar

  • Lookup Based on Criteria

    Hi all,


    I have weekly update to make on my staff on their overtime hours.I have column B which has staff name in data validation scroll down list.In column C is where I am suppose to key in my respective staff id's.I would like to create a formula in which when I choose John in the column for staff name, the employee id should automatically pop out in column C. I tried the IF function and it worked perfectly . However, it only allows up to 7 staff. How do I expand the formula to include all 13 of my staff ? I have attached a simple sample for reference where the formula should be in column C that reads "Staff ID". Appreciate help. Thanks guys.

  • IF Function

    Hi All,

    I want to create an IF formula to generate the staff id automatically whenever I type a staff name. Column A will be staff name and column B will be the IF formula.However, it appears that the IF formula does not allow more than 7 options. Example=if(A12="John","2233422",if(A12="Michael","5565342","")

    I have 13 staff in my team and I would like to include all 13 in the formula. Is there any other way? thanks !

  • SUMIF Function

    Re: SUMIF Function


    Hi Domenic,


    Thanks for the reply. I will try this formula with the original spreadsheet. I hope you can help me with another problem. I have a speradsheet which contains all the names of the staff with their quality score of the month. What I would like to do is create a formula that shows how many items of their work which was reviewed that has more or equal to 1 error.So, column A has the name of the staff and column B has the number of the error.Since the file has about 800 entries which is the total for the whole month of January, I need to formulate this for all the staff. I have attached the simplified version of the file for January. Thanks for your time and assistance.

  • SUMIF Function

    I need some help to manage my data. I have the same data ( but not necessarily on the same cell for everyday) for everyday of the month.Now I need to calculate what is the total for an individual for the whole month.Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work.
    =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7)


    I have simplified the file as an attachment below.Appreciate if anyone can help as the original file is for 6 months and I need a way to compute the data. Thanks !

  • Combine vlookup and sum?

    I have a same format of data on every day of the month.Each day of the month has a separate sheet.The first column is name and the subsequent columns are data(numbers). However, on some of the days of the month, the cells for a particular person is not the same.


    Example, on April 3 John's data might be on cell F2 but on April 29, his data might be on cell F1.


    I need to formulate to calculate John 's data for the whole month of April. I thought of using vlookup and sum formula but I am not sure how to combine both the formula.



    Appreciate assistance.

  • vlookup for text

    Hi,


    This is my first post. I am trying to formulate the following:


    =vlookup(John, Range, 3 , FALSE)


    However, the formula doesn't seems to work. When I change the first columns that contains names ( John, Jane...) to a number, then the formula works. How do I make this formula work with text as above ? Thanks.

  • Vlookup for text

    Hi,


    I am trying to formulate the following:


    =vlookup(John,Range,3,FALSE)


    However the formula doesn't work. When I change the first column which contains names ( John,Jane..) to a number, then the value works.


    Can anyone assist me how to make this formula work ? Thanks.