SUMIF or maybe something else?

[kassysimon]

Hi guys.

The spreadsheet I'm designing is a time sheet to calculate how much time it takes to perform predetermined tasks.

For example:

Cell A1 to A10 have drop downs to select the tasks

Cell B1 to B10 populates the predetermined time the task should take via VLOOKUP

Cell C1 to C10 is where the user enters the start time.

Cell D1 to D10 is where the user enters the finish time.

Cell E1 calculates the total time spent on all tasks.


What id like is for a Cell F1 to calculate the time spent working on only one specific type of task that is selected in Cell A1-A10 and not calculate the other tasks times.

I tried SUMIF but i couldnt get it to work. I may have just missed understood the syntax. =SUMIF(A1:A10,A1:A10 = "the specific task")

I thought of using =IF(A1="the specific task", (D1-C1), "") in a new page, summing up the results and deducting it from Cell E1 but this seems inefficient considering its 111 lines of task.

Id also like to exclude the spefic task from E1 if possible.

How do you recommend do this?

Thanks in advance.

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Richard Buttrey]

Hi,

You could assist us to help you if you upload the workbook in which you have manually added the result(s) you expect to see and tell us which are the result cells.

[kassysimon]

Here you go guys.

I hope this helps.

[FDibbins]

THis will give you the sum you wanted...
=SUMIF($B$2:$B$17,"CALCULATE THIS SPECIFIC TASK TIME ONLY",F2:F17)

You almost had it, the syntax is criteria-range,criteria,sum-range

OK, Im a little hazy on your CF question? It sounds like you want to highlight all rows with address A in them - and then by implication, all rows with B etc?

[kassysimon]

Hello. And thanks.

With regards to the CF. Yes. Exactly! If a task is associated to the same address multiple times id like to highlight that so i can see which address are requiring additional visits.

Thank you again.

[FDibbins]

I think you will need to set up a new rule for each address (although I have a feeling you will have far too many addresses for this?)...

1. highlight the range you want to apply the conditional formatting to (A2:F17 in your sample)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$A2="address A"

[kassysimon]

Hello.

You are correct in your first assumption.... Far too many addresses.

I believe your formula will only work with predetermined addresses.

My requirements are for unique addresses that are used multiple times.

Sounds like a sticky one hey?

Thanks all the same.

[ELeGault]

How many Unique addresses do you expect to find? and if they only appear once do you NOT want them to be colored?

If that is the case I would add 2 "Helper Columns"

Insert two columns, Inside B put
Label it appropriately UDupCount or something like that (Unique Duplicate Count) - The following will find the second occurrence of the data in what is now C column and assign it a chronological number value.
B2

Please Login or Register  to view this content.

Now that you have unique numbers to run off of, you need them to repeat do this in A like this..
A2

Please Login or Register  to view this content.

Now just as FDibbins explained you can create some CF, only now you know that they will count up from 1 - max expected addresses. If within reason you can map these out

1. highlight the range you want to apply the conditional formatting to (A2:F17 in your sample)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$A2="address A"

Only change that range to C2:H17 and your formula would still be bassed off of the A column as shown in step 4 only it would say =$A2=1 and then the next =$A2=2 etc...

Will take some setup on your end to map out the max occurrences but that would do it. A will have 0's in it, this indicates that it is the only occurrence of that corresponding address. Can also create the CF for that or leave it without CF ...

May be a bit much, but it is a work around for what you're playing with.

Enjoy,

[kassysimon]

ok..i almost have it.

Curious as to why column A refers to the first entry as -1?

Thanks

[ELeGault]

If you have headers in A1 and B1 that should not be the case...if you did not put a label then there is an extra space so it is subtracting that from 0, making the first dup -1...so just add the headers and you should be good.

[kassysimon]

Thats a bingo!

You got it in one!

You guys are great!!

Thanks for all your help.

[ELeGault]

Always a pleasure - Please be sure to click any stars to add rep if you fell anyone was helpful throughout the thread, but more importantly be sure you set the thread to Solved under thread tools near the top -

Cheers

[kassysimon]

Cheers guys.