Help with VLOOKUP and nested IF formula, if that is what is required to solve my problem?

[R Leman]

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

[#2]

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

[#3 · Accepted solution]

Dear R Leman

plz chk the attached file Green colored cells

[#4]

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

[#5]

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

[#6]

Welcome