Adding countif and number in the same cell

[Jason1973]

I have a question.
I have one column that adds count if function. That part is fine, but now I have to include an additional ‘WTE column’ where previously only 1.00 (meaning one job advertised for that team), now i need to count 2.00, which means the countif function for that department needs to have the number value included in the count.
e.g team Jason (count if) job: admin and number of jobs: 2.00. I want this to link to the team aggregate for that week to 2 jobs.
I hope someone can help
thanks

 

[Jason1973]

Hello this is my answer below, I am very grateful for your time.

1. The sample data you posted, what sheet is that from? that is from a tab marked 14th May

2. What is the EXACT range that the sample data you posted above exists in (your example does not show row or column headers)?
So, what cell address does the word "Band" appear in? cell B18 but I only want to add the 'I' column' that is numerical and add to the overall tracker.
And what column letter in "WTE" located in? 'I' column, dates where jobs are advertised are broken down into tab and linked to the overall tracker, where initially the department was counted through a 'countif' function'

3. The formula you posted, is that formula what is currently in your "WTE" column? no, I want to include that in the 'countif' formula too to the overall tracker for that team that have advertised jobs.

4. Your formula mentions BOTH sheets "14th May 2021" and "Data & Links". You have posted a sample of one of those sheets. Can you post a sample of the other one? sure. but I am wanting to add the i column to tally with the 14th May data. if that makes

Vacancies - Finance Directorate .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1			Tracker	VACANCIES		Data and Links Tab (Click here)				Key:	Topic:
2											Team replied
3										Yes	Week Focusing on.
4		Issue Number	W/c	Emails Prepared? (Y/N)	Current Week	Total	Capital Cash	CCG Mergers	CDAO *Formerly PAT	CFO Private Office	Commercial Medicines Devices/Unit	Finance, Planning & Delivery	Financial Control	Governance and Legal	LTP Implementation	Operations, Planning and Performance	Oversight and Assessment	Planning	Pricing and Costing	Specialised Commissioning	Strategic Planning Finance
17		12	14/05/2021	Yes	Yes	19	0.00	0.00	4.00	0.00	1.00	3.00	0.00	0.00	0.00	0.00	0.00	0.00	3.00	7.00	1.00
Tracker

Cell Formulas
Range	Formula
F17	F17	=SUM(G17:U17)
G17	G17	=COUNTIF('14th May 2021'!E$5:E$34,'Data & Links'!C$1)
H17	H17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$2)
I17	I17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$3)
J17	J17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$4)
K17	K17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$5)
L17	L17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$6)
M17	M17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$7)
N17	N17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$8)
O17	O17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$9)
P17	P17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$10)
Q17	Q17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$11)
R17	R17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$12)
S17	S17	=SUM(COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$13))+1
T17	T17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$14)
U17	U17	=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$15)
B17	B17	=+B16+1
C17	C17	=+C16+7

Named Ranges
Name	Refers To	Cells
Teams	='Data & Links'!$C$1:$C$15	G17

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:D32	Cell Value	contains "Yes"	text	NO
T17	Cell Value	between 1 and 5	text	NO
T17	Cell Value	>">1.00"	text	NO
T17	Cell Value	>1.5	text	NO
T17	Cell Value	>1	text	NO
E6:E32	Cell Value	contains "Yes"	text	NO

 

[Joe4]

I still don't see the relevant information from your "Data & Links" tab.

Both your formula attempts include references to them:
=COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$1)
=SUM(COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$13))+1

It is important for us to see all the data involved so that we can see what is going on.

I think we may have hit an impasse here, as we seem to have a failure to communicate. I am not sure how else to ask you for what we need in a way that you understand and provide it to us.
Your best bet may be to upload your workbook to a file sharing site (first removing any sensitive data), and provide a link to it so that people here can download it, and see all the sheets, data, and formulas. And also "hard-code" in some examples of your expected results on that uploaded workbook, so people can see EXACTLY what you are after (and where you want it).

Unfortunately, I do not have the ability to download files from my work computer, so I probably won't be able to help you (at least not until much later tonight when I have access to another computer). But another user may be able to.

 

[Jason1973]

thank you anyway. It would be best to see the file so you can understand where I am coming from. you have tried your best to help me and for that I am grateful. I am sure I will be able sort out the solution.

I just want to tab(s) where the WTE 'I' column to link to the overall tracker for that week and that attached to that team (which is counted as a countif). basically I just want to add to the formula (=COUNTIF('14th May 2021'!E$5:E$34,'Data & Links'!C$1) and look how to capture the WTC from the tab titled '14th May 2021' if you can't help that is fine. thanks for perceiving though. god bless