Question about Microsoft Excel 2007 Home and Student Edition for PC

I need to create a formula to calculate the total time spent on calls by each operator 1-4 I am working with excel.I would applreciate any help I can email you the worksheet if needed. my email is suzyqsims06@yahoo.com. Thanks for the help Brenda

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.

BTW, sent you a similar email about this.

Posted on May 08, 2009

If your operators' time is listed in cells B2 through B5, for example, the formula to calculate the total time would be:

=SUM(B2:B5)

Let me know if this is not what you're looking for. Thanks!

Posted on May 19, 2009

Hi,

a 6ya expert can help you resolve that issue over the phone in a minute or two.

best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).

click here to download the app (for users in the US for now) and get all the help you need.

goodluck!

Posted on Jan 02, 2017

Hide All

Percentages are calculated by using the following equation:

amount/total = percentage

Where percentage is in decimal format.

What do you want to do?

Calculate the amount if you know the total and percentage
For example, if you purchase a computer for $800 and there is an 8.9% sales
tax, how much do you have to pay for the sales tax? In this example, you want to
find 8.9% of 800.

Example
The example may be easier to understand if you copy it to a blank
worksheet.

- Create a blank workbook or worksheet.
- Select the example in the Help topic.
**Note**Do not select the row or column headers.

Selecting an example from Help - Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return
the results, press CTRL+` (grave accent), or on the
**Formulas**tab, in the**Formula Auditing**group, click the**Show Formulas**button.

Calculate the percentage if you know the total and amount For example, if you score 42 points correctly out of 50, what is the percentage of correct answers?

Example The example may be easier to understand if you copy it to a blank worksheet.

- Create a blank workbook or worksheet.
- Select the example in the Help topic.
**Note**Do not select the row or column headers.

Selecting an example from Help - Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return
the results, press CTRL+` (grave accent), or on the
**Formulas**tab, in the**Formula Auditing**group, click the**Show Formulas**button.

Simple way Amount/Total*100

Apr 30, 2011 | Computers & Internet

Here is the syntax: =SUMIF(Sheet2!A1:A3,"> 1", Sheet2!B1:B3)

This says if the cells A1 through A3 in worksheet "Sheet2" are greater than 1 then return the values from worksheet "Sheet2" cells B1 through B3.

Change the worksheet name Sheet2 to your worksheet name and change the cell references to the ones you need.

This says if the cells A1 through A3 in worksheet "Sheet2" are greater than 1 then return the values from worksheet "Sheet2" cells B1 through B3.

Change the worksheet name Sheet2 to your worksheet name and change the cell references to the ones you need.

Apr 17, 2009 | Computers & Internet

Use the following formula as an array formula, you need to hit "Shift+Ctrl+Enter" after entering the formula. Lets say you have the Name in column "B", the Age in column "C" and the amount paid in column "D". Here is the formula.

=SUM((B2:B7="Abhilash")*(C2:C7>0)*(D2:D7))

It will calculate the amount paid if the name =Abhilash" and the Agr is greater than zero.

=SUM((B2:B7="Abhilash")*(C2:C7>0)*(D2:D7))

It will calculate the amount paid if the name =Abhilash" and the Agr is greater than zero.

Apr 11, 2009 | Microsoft Works 8.0 for PC

Yes you can create the formula outside the cell or you can create it one time and copy it down to the other cells. That way it will populate once it is used.

The formula should be Base Rate * .05 to get the result then add the result back to the base rate.

The formula should be Base Rate * .05 to get the result then add the result back to the base rate.

Dec 18, 2008 | Microsoft Excel for PC

One in general should not use Find & Replace.

I would recommend always creating the first formula manually by selecting the cells. This will avoid common errors like extra spaces.

I would recommend always creating the first formula manually by selecting the cells. This will avoid common errors like extra spaces.

May 11, 2008 | Microsoft Excel for PC

Look into the =SUMIF function, it sounds like this may be what you are looking for.

Hope this helps!

Hope this helps!

Apr 09, 2008 | Microsoft Excel for PC

Hi, I was looking to create a timmer. with stop and start option on excel.I have found the anser withyahoo group answers, that was help full and free, i got the VB macro programee. if you still can help me , here is the query,

I need a timer with stop and start option,

when i click on the star button i want the present time coming up on a cell(any cell) and when i click on the stop, i need to get the presen tme on the other cell, i need to clculate the time spent between the 2times.

i also need the time geting accumalated in the different sheet to calculate, how many times i have clicked and the difference.

Thank you for you any help you provide.

Regards

Rochboy

I need a timer with stop and start option,

when i click on the star button i want the present time coming up on a cell(any cell) and when i click on the stop, i need to get the presen tme on the other cell, i need to clculate the time spent between the 2times.

i also need the time geting accumalated in the different sheet to calculate, how many times i have clicked and the difference.

Thank you for you any help you provide.

Regards

Rochboy

Apr 03, 2008 | Microsoft Access 2000 (077-01277) for PC

Try using the SUMIF function for one condition or multiple conditions. Use the SUMIF worksheet function to create a total value for one range based on a value in another range. For example, for every cell in the rage B5:B25 that contains the value "Northwind", the following formula calculates the total for the corresponding cells in the range F5:F25. Formula:

=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

Dec 09, 2007 | Computers & Internet

If I understand correctly, you want to figure the normal wages at 40 hours and less. If there is more then 40 hours, calculate the normal 40 hours, then calculate the hours overtime (time and a half) and add them to get a total.

B5 columns are filled with this:

=IF(A5>$B$1,($B$2*$B$1)+((A5-$B$1)*($B$3)),A5*$B$2)

NOTE: The $ sign doesn't refer to money, it refers to an absolute reference so when copying a formula, the cell references with a $ sign are fixed and don't adjust according to the relative position to where it's being pasted.

Please rate as FixYa! or ask please clarify. ThankYa!

B5 columns are filled with this:

=IF(A5>$B$1,($B$2*$B$1)+((A5-$B$1)*($B$3)),A5*$B$2)

NOTE: The $ sign doesn't refer to money, it refers to an absolute reference so when copying a formula, the cell references with a $ sign are fixed and don't adjust according to the relative position to where it's being pasted.

Please rate as FixYa! or ask please clarify. ThankYa!

Oct 22, 2007 | Microsoft Excel 2003 (06503995)

try using the formula "sumif". It allows you to sum by category/name.
Good luck!
D.

Sep 12, 2007 | Microsoft Office Standard for PC

387 people viewed this question

Usually answered in minutes!

can we assume the operators are recording their start and end times of calls? if not, how are you getting this information?

you can email me your worksheet so far at jerry@jerry-russell.com

Have you been able to resolve your sumif formula?

×