# I am having trouble creating a formula.

Microsoft Excel 2007
September 2, 2010 at 18:51:43
Specs: Windows XP, 2.4GHZ / 2GB
 I have created a spreadsheet to capture payroll for our truck drivers. They are paid by the mile and there are different rates depending on the number of miles in a trip. I have a sheet for each driver and each sheet covers a one week (7 day) period. So there is a possibility of 7 days of miles that fit into one of three categories (>500, <425,>=425&<=500). The other factor that affects the rate of pay is the type of equipment being used (RT or V). What I need to come up with is the total miles for the week under each of 6 possible combinations. Each of the 6 possible answers would appear in their own cell. I could give RT & V a numerical value if necessary. I hope that I have explained it well enough for someone to help.

See More: I am having trouble creating a formula.

#1
September 2, 2010 at 22:33:15
 It might help if you gave us the layout of your sheets.e.g. What column(?) are RT and V in.Do each of the mileage ranges have their own column or are they all in one column and the mileage is in the next column?etc?etc?

Report •

#2
September 3, 2010 at 05:29:55
 The days of the week from Monday to Sunday are in column A in cells 10 to 16. The miles per day are in column F, cells 10 to 16. The RT or V identifier is in column I, cells 10 to 16. The formula to collect the total miles during the week using V (vans) and with individual trip miles greater than 500 miles is in K22. The formula to collect total miles during the week using V (vans) and with individual trip miles equal to or between 425 and 500 miles is in cell K23. The formula to collect total miles during the week using V (vans) and with individual trip miles less than 425 is in K24. The cells K25, K26 & K27 contain the same formulas as above except for RT (roll tite) equipment. Is this the information that you need (I hope)? If not, please let me know and I will provide whatever else is needed. Tks.

Report •

#3
September 3, 2010 at 07:31:12
 Hi,I created a table of values to go into cells L23 to N27Each row contained the data for the group, as follows:``` L M N 22 V 501 9999 23 V 426 500 24 V 0 425 25 RT 501 9999 26 RT 426 500 27 RT 0 425 ```Then in cell K22 I entered this formula:=SUMIFS(\$F\$10:\$F\$16,\$I\$10:\$I\$16,"="&L22,\$F\$10:\$F\$16,">="&M22,\$F\$10:\$F\$16,"<="&N22)Now drag the formula down, to extend it to K27K22 to K27 will now show the miles for each group.RegardsPS SUMIFS is not available in Excel 2003 or earlier, but other functions such as SUMPRODUCT could be used instead, if this workbook had to be run on Excel 2003 or earlier.

Report •

Related Solutions

#4
September 4, 2010 at 10:01:31
 Humar, Thanks. I tried your formula but it didn't work. I don't understand where the L22, M22 & N22 values are coming from. I will try to show my spreadsheet below: 8 A B C D E F G H I J K 9 Dates Trip # Hrs Wrk Wait Tm KM Miles Drops Lyvr RT/V Wrk Type App10 Au 1 01 0 0 800 497 4 0 RT R11 Au 2 02 0 0 600 373 2 0 V R12 Au 3 03 0 0 1000 622 4 0 RT R 13 Au 4 04 0 0 500 311 4 0 RT R 14 Au 5 05 0 0 700 435 2 0 V R15 Au 6 06 0 0 900 559 4 0 RT R16 Au 7 07 0 0 1200 746 2 0 V R17181920 Regular Hours 21 Waiting Time22 Mileage - Vans >500 miles23 Mileage - Vans 425 - 500 mi 24 Mileage - Vans <425 miles25 Mileage - Roll Tite >500 m26 Mileage - Roll Tite 425 - 500 m27 Mileage - Roll Tite <425 m28 Drops/Picks - Vans29 Drops/Picks - Roll Tites30 LayoversOK, A to K are the columns and their headings. 8 to 30 are the actual row numbers and the formulas that I am trying to create goes in K22 to K27. I should be able to then modify the formula to do a similar calculation for the Drops and Picks in K28 & K29. I hope this makes it more clear. Unfortunately the message as I typed it is not formatted the same in this display.

Report •

#5
September 4, 2010 at 10:41:15
 Humar, Thank you very much. I wasn't aware of the "SUMIFS" function but with your example I was able to figure out what was wrong and make it work. Thanks again, I couldn't have done it without your help.

Report •

#6
September 4, 2010 at 11:00:22
 Hi,EDIT - I see you just posted to say you've got it working - great to hear that. So ignore the rest of this post, although you might look at the note at the end about posting data.I don't understand where the L22, M22 & N22 values are coming fromI said in my response: I created a table of values to go into cells L23 to N27Each row contained the data for the group, as follows:This should have said L22 to N27This table provides the source parameters for the formulas to work on.500 & 9999 is just one way of identifying mileage of 500 miles and more.By using a table, I was able to use one formula that could be dragged down in column K from K22 to K27.Each formula could have been different - each containing V or RT and the mileage values such as >500.The table approach just simplified the way the formulas were created.Using the mileage and vehicle type data from your last post, (column F & I and rows 10 to 16, this is what I got:``` A ... K 22 Mileage - Vans >500 miles 746 23 Mileage - Vans 425 - 500 mi 435 24 Mileage - Vans <425 miles 373 25 Mileage - Roll Tite >500 m 1181 26 Mileage - Roll Tite 425 - 500 497 27 Mileage - Roll Tite <425 m 311 ```Are these results correct?Please, if posting data, to help line up columns, put your data between
and
tags that you can insert using the 'Pre' icon above the reply box. Then use the Preview button and edit as required. To preview again, check the 'Check To Show Confirmation Page Again' box and click 'Confirm and see post'Regards

Report •

#7
September 5, 2010 at 14:54:47
 HumarYes your results are correct. Thanks again for the help and for the info re posting data in columns.rct

Report •