- If you need clarification, ask it in the comment box above.
- Better answers use proper spelling and grammar.
- Provide details, support with references or personal experience.
Tell us some more! Your answer needs to include more details to help people.You can't post answers that contain an email address.Please enter a valid email address.The email address entered is already associated to an account.Login to postPlease use English characters only.
Tip: The max point reward for answering a question is 15.
There are two ways to solve it, with a macro and without.
In either way, the concept is to determine the difference between rows, if it is more than 1 hour, then you have detected the missing row.
With a formula, you can tell excel to put true if it is the case and correct it manually.
For a macro, basically you detect the same thing and then insert a line with all the appropiate information. I can write the code for you, but i would prefer to have the sheet. You can send me the information to my mail email@example.com
Brenda, Your problem is not so much the formula but the cells that contain the hours for each call. Since they contain the text “hrs” (for example “2.25hrs”) this turns the whole cell to text, not numeric, which will always add up to zero in Excel. So do two things: -Change the cells in column H (Time Spent) to numeric (2.25 instead of 2.25hrs) – you might want to change the column header to Time Spent In Hrs just to b clear -Use this formula =SUMIF(E8:E26, 1, H8:H26) For the formula change the middle number to correspond to the operator ID – for example for operator 2 change the formula to =SUMIF(E8:E26, 2, H8:H26) This should do it.
You can refer to cells that are on other worksheets by perpending the name of the worksheet followed by an exclamation point (!)
to the cell reference. In the following example, the AVERAGE worksheet
function calculates the average value for the range C1:C10 on the
worksheet named Marketing in the same workbook.
Refers to the worksheet named Marketing
Refers to the range of cells between C1 and C10, inclusively
Click the cell in which you want to enter the formula.
I love vlookup!
Suppose you have 1 worksheet with song numbers and titles in Row 1, Cols A:B:
123 Love Me Tender
234 Blue Suede Shoes
Another worksheet has song number and performer in Row 1, Cols A:B
123 Elvis Presley
234 Carl Perkins
Notice there is NO performer for song number 345 in the 2nd worksheet.
Now in the 1st work sheet, cell C2 insert this LOOKUP function: =LOOKUP(A2,Sheet2!A:B)
Copy that cell to row 3 and row 4 in Col C. You should get a Performer for all songs even though there is not a song number 345 in the performer worksheet.
Help me out Mr. VLOOKUP.
Insert this VLOOKUP function in cell C2 of the first worksheet: =VLOOKUP(A2,Sheet2!A:B,2,0)
Copy that cell to row 3 and row 4 Col C. You should get the performer names for the 1st 2 songs, but not for 345 Dixie. The result should be #N/A.
That means VLOOKUP could not find a DIRECT match for song 345 in the second worksheet.
That is why I prefer VLOOKUP over LOOKUP.
I have found this explaination of the VLOOKUP parameters helpful:
1. Needle (A2)
2. Haystack (Sheet2!A:B)
3. RELATIVE Col containing result (2)
4. Need DIRECT MATCH ONLY (0)
Hope this helps.
Let me know if you have any questions.