PDA

View Full Version : How to use vlookup and countif together in one formula?



tommyl18
01-04-2014, 08:05 AM
Hi thanks for taking the time to look at this thread.

I have been trying to work out the best way to create a formula or formulas or even a macro to answer my following query.

I have staff that work for me on a regular basis for each day they work I make a specific amount of profit off each person.

I need to automatically work out how much profit each person is making me. I have attached photos to help explain, The only formulas I can think of are vlookup and countif together but will I need a lot of vlookup formulas for it to work?

Your help would be much appreciated thanks in advance.

I currently have a validation list for each specific person ie, Tom half day, Tom before tax, Tom after tax etc then I have a vlookup formula for there specific day rate in relation to that.

For example, I need to see how many times "Tom half day before tax" shows up in column G on the demolition sheet and multiply this by the profit shown in the table in the profit and loss sheet, once I have found that out I then need to do the same for

Tom full day before tax, Tom full day after tax, tom half day after tax. Once i have the totals from them I then need to add them together to give me the result which I need to go into C:3 in the profit and loss sheet. Hope this makes sense!!

Bob Phillips
01-04-2014, 10:20 AM
Can you post the workbook, it is very difficult to work off of pictures.

tommyl18
01-05-2014, 03:56 AM
yea no probs how can I do that it only seems to let me attach photos?

Aussiebear
01-05-2014, 04:23 AM
In the reply box, click on "Go Advanced" & scroll down to "Manage Attachments" and click on that then follow the prompts from there.

tommyl18
01-05-2014, 05:28 AM
ok thanks

tommyl18
01-05-2014, 09:22 AM
did the workbook manage to download ok?

tommyl18
01-05-2014, 09:58 AM
please anybody help me im desparate lol

Bob Phillips
01-05-2014, 10:15 AM
First of all, I have to say that I think you are going about this the wrong way. This looks like a job that is crying out for a database centric solution. Whatever you do to make this work, you are going to be forever tied to a loose solution, one that you will probably spend more time maintaining than it is worth, every change will be a chore.

I got it working after a fashion, but only after adding this extra formula to the demolition sheet, =VLOOKUP($G5,'Profit and loss'!$G$5:$L$16,6,FALSE), in J5 down, so as to get the profit on the work sheets. Then I used

=IFERROR(SUM(IF(LEFT(INDIRECT($B5&"!G5:G30"),FIND(" ",INDIRECT($B5&"!G5:G30"))-1)="Tom",(ISNUMBER(MATCH(INDIRECT($B5&"!G5:G30"),$G$5:$G$16,0)))*(INDIRECT($B5&"!J5:J30")))),"")

on the Profit & Loss sheet.

But this won't work for any of the other work sheets, as they are not the same format as the Demolition sheet.

And is each job a separate workbook, meaning that you will be copying this complexity to every workbook?

As I say, storing the data differently and querying would be a far better solution.

tommyl18
01-05-2014, 12:16 PM
Hi thanks for the advice, Im completely new to all this I have been using Notepad for the last 8 years to store all my data and now thought it is about time I started automating everything. Each job will be a separate workbook yes and once I had got this finished I was going to create a master workbook and try and use some vba coding to gather all the data together.

Bob Phillips
01-05-2014, 12:22 PM
I would even more so suggest then that you start from the other end.

Build tables of

customer details
staff members + rates
job types -demolition etc.
etc. etc



This could be a separate workbook, or preferably, a database. Build an addin with the code in it, and create a ribbon to add some buttons to do the stuff you want.