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.

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?

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.

Hi, I created a table of values to go into cells L23 to N27

Each 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 K27

K22 to K27 will now show the miles for each group.

Regards

PS 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.

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 App

10 Au 1 01 0 0 800 497 4 0 RT R

11 Au 2 02 0 0 600 373 2 0 V R

12 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 R

15 Au 6 06 0 0 900 559 4 0 RT R

16 Au 7 07 0 0 1200 746 2 0 V R

17

18

19

20 Regular Hours

21 Waiting Time

22 Mileage - Vans >500 miles

23 Mileage - Vans 425 - 500 mi

24 Mileage - Vans <425 miles

25 Mileage - Roll Tite >500 m

26 Mileage - Roll Tite 425 - 500 m

27 Mileage - Roll Tite <425 m

28 Drops/Picks - Vans

29 Drops/Picks - Roll Tites

30 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.

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.

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 from

I said in my response:I created a table of values to go into cells L23 to N27

Each 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 <pre> and </pre> 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

Humar Yes your results are correct. Thanks again for the help and for the info re posting data in columns.

rct

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History