Count Value in Cell next to specific text

VBA_Confused

New Member
Joined
Aug 27, 2014
Messages
6
Hi all,

After some great help with my first query here, I have another query I hope you can help with!

I have a timesheet style database, each staff member is allocated a row and then for each day they have to choose a description of activity that day 'Sick' 'TOIL' etc. In the column next to the description cell is another for them to enter the hours allocated to that activity.

What I want to be able to do is count the number of hours that each worker allocates to each activity so at the end of each month I can see how much time is being dedicated to certain activities.

So, worker 1 is on Row 6. In G6 they choose 'Sick' from a drop list and in G7 they type 7.5 for the hours. How do I search the whole of row 6 for 'Sick' and then each time it appears count the hours in the column one to the right and then display the total for all time allocated to 'Sick' for Worker one??
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Thanks Joe, please correct me if I'm wrong, but wouldn't the 'COUNTIFS' function count how often 'Sick' was entered, rather than the value in the column right of 'sick'? I want it to count the number that is in the cell offset by one column to where 'Sick' appears as the amount of time people work each day varies so I can't assume that each day is X number of hours?
 
Upvote 0
Sorry Joe, i'm sure it's just me being dense but still can't work out how it helps. I've included a picture below and a new description as I think it might be the way i'm described it isn't helping.

ScreenShot2014-08-28at221556_zpsa343da77.png


In the image is a couple of rows from the spreadsheet. I want to be able to add up the numbers in the column to the right of 'Sick' every time 'Sick' has been selected from the drop down list. In my head, I need to do a sum formula that first sets the range by searching for 'Sick', however many times it appears, and then offsets each time it appears by one column to the right before adding all the selected values together. Does that make sense?

The row in question here is 6 and the columns are E and K.
 
Upvote 0
Is anyone able to give another me another pointer on this? I've tried a lot of things but still struggling in a big way! :confused:

Thanks
 
Upvote 0

Forum statistics

Threads
1,223,287
Messages
6,171,219
Members
452,391
Latest member
BHG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top