Forum Discussion
R Leman
Aug 26, 2018Copper Contributor
Help with VLOOKUP and nested IF formula, if that is what is required to solve my problem?
I am trying to find the number of staff employed in a month by asking Excel to look up in a column for the words 'Basic Salary' and then look x columns across a table to see if there is a value greater than 0, if that is the case to return a value of 1 and then add up each of those results so that we know how many staff are working each month. I would like to copy the formula across cells so I can continue to expand the spreadsheet.
Feb-17 | Mar-17 | Apr-17 | May-17 | Jun-17 | Jul-17 | Aug-17 | Sep-17 | Oct-17 | Nov-17 | Dec-17 | Jan-18 | |
Basic Salary | 4,200 | 4,200 | 4,200 | 4,200 | 2,980 | 638 | 50 | 48 | 50 | 48 | 50 | 50 |
Commissions | 6,692 | 6,124 | 2,424 | 7,885 | 2,610 | 645 | ||||||
Guarantees | ||||||||||||
Employers NI | 1,533 | 1,455 | 944 | 1,698 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Employee Benefits | ||||||||||||
Staff Recruitment Fees | ||||||||||||
Vehicle - Car Allowance | 900 | 900 | 900 | 900 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Basic Salary | 46 | 46 | 46 | 46 | 5,000 | 5,000 | 5,000 | 5,000 | ||||
Commissions | 692 | 1,516 | 1,227 | 1,735 | 2,127 | 1,386 | 1,386 | 1,386 | ||||
Guarantees | ||||||||||||
Employers NI | 91 | 204 | 165 | 235 | 972 | 870 | 870 | 870 | ||||
Employee Benefits | ||||||||||||
Staff Recruitment Fees | ||||||||||||
Vehicle - Car Allowance | 600 | 600 | 600 | 600 | 600 | 600 | 600 | 600 | ||||
Basic Salary | 3,750 | 3,750 | 3,750 | 2,412 | 441 | 50 | 48 | 50 | 50 | |||
Commissions | 2,731 | 6,983 | 1,641 | 1,274 | 1,030 | |||||||
Guarantees | ||||||||||||
Employers NI | 859 | 1,446 | 709 | 0 | 0 | 0 | 0 | 0 | 0 | |||
Employee Benefits | ||||||||||||
Staff Recruitment Fees | ||||||||||||
Vehicle - Car Allowance | 425 | 425 | 425 | 0 | 0 | 0 | 0 | 0 | 0 | |||
Basic Salary | 2,167 | 2,167 | 2,167 | 2,167 | 2,167 | 1,612 | 2,167 | |||||
Commissions | 0 | 0 | 0 | 555 | 0 | 450 | 74 | |||||
Guarantees | 450 | 450 | 0 | 0 | 0 | 0 | ||||||
Employers NI | 245 | 307 | 307 | 322 | 245 | 238 | 255 | |||||
Employee Benefits | ||||||||||||
Staff Recruitment Fees | ||||||||||||
Vehicle - Car Allowance | 290 | 290 | 290 | 290 | 290 | 214 | 290 | |||||
Basic Salary | 3,333 | 3,333 | 3,333 | 4,001 | ||||||||
Commissions | 79 | 124 | 1,260 | 435 | ||||||||
Guarantees | ||||||||||||
Employers NI | 436 | 442 | 599 | 534 | ||||||||
Employee Benefits | ||||||||||||
Staff Recruitment Fees | ||||||||||||
Vehicle - Car Allowance | 425 | 425 | 425 | 353 | ||||||||
Dear R Leman
plz chk the attached file Green colored cells
- S Akkaz H JaffaryBrass Contributor
- R LemanCopper Contributor
Perfect, this did it exactly - really useful, have been trying to solve this one for a week! Thanks very much for your help, much appreciated
- Philip WestSteel Contributor
I'm attaching a spreadsheet that I think works. It uses this formula to count up the number of basic salaries in a given month:
=COUNTIFS(tbl_data[[Column1]:[Column1]],"Basic Salary",tbl_data[Feb-17],">0")
countifs lets you count something that matches more than 1 criteria, in this case that in column 1 the row says Basic salary, and in the Feb-17 column there is a number in there that is greater than 0.
When you drag this formula right, 'month' column will move right as well
- R LemanCopper Contributor
Thanks doesn't quite do the copy across. Have checked other reply to my original and it works - thanks very much for your help though
- S Akkaz H JaffaryBrass ContributorWelcome