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

 

[Joe4]

In these cases, it is often helpful to us if we can see what your data looks like, and what you want your expected results to look like. So if you could post a small example of your data with these details, it would be most beneficial.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Jason1973]

here is the formula to capture the information =COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$1)
I need to include 2.00 (Column far right) to the above which is captured from the enclosed sheet. sorry having difficulty uploading the XL2BB file.

Band:	Role:	Ref:	Team:	FTC Permanent Secondment (specify WTE):	Closing Date	Link: e.g. (EoI, Trac or jobs.nhs.uk)	WTE
8d	National Programme of Care Senior Manager (Internal Medicine)	990-1-EI4947-CE	Specialised Commissioning	Permanent	16/05/2021	Link	1.00
8d	Senior Scientific Advisor	990-1-EI4961-CE	Specialised Commissioning	Permanent	19/05/2021	Link	1.00
8c	Delivery Lead (Senior Programme Manager)	1567	Specialised Commissioning	FTC/Secondment	14/05/2021	Link	1.00
8c	Senior Financial Planning Lead	990-2-EI4788-CE	Finance Planning and Delivery	Permanent	16/05/2021	Link	1.00
8b	Senior Analytical Manager	990-1-EI4925-MI	CDAO	Permanent	16/05/2021	Link	1.00
8a	Finance Planning Manager	990-2-EI4900-CE	Finance Planning and Delivery	Permanent	23/05/2021	Link	1.00
8a	Financial Planning Manager	1542	Finance Planning and Delivery	FTC/Secondment	21/05/2021	Link	1.00
7	Analyst	990-1-EI4963-SE	CDAO	Permanent	18/05/2021	Link	1.00
7	Genomics Governance Support Officer	990-1-EI4743-CE-A	Specialised Commissioning	Permanent	18/05/2021	Link	1.00
6	Payment Development Officer	990-1-EI4946-LN	Pricing and Costing	Permanent	16/05/2021	Link	2.00
6	Analytical Officer	990-1-EI4962-SE	CDAO	Permanent	18/05/2021	Link	1.00
6	Payment Development Officer	990-1-EI4946-LN	Pricing and Costing	Permanent	16/05/2021	Link	1.00
6	Analytical Officer	990-1-EI4962-SE	CDAO	Permanent	18/05/2021	Link	1.00

 

[Joe4]

I am afaid I still do not understand your issue.
Remember, while the problem is very familiar to you, all that we know about it is what you have shared with us.

Questions I still have:
- The formula that you posted, what sheet is it on, and what cell have you placed it in?
- Your formula references multiple sheets, but you only pasted a section of one. Can you post samples of the other relevant information?
- Based on all the sample data that you post, can you also post your expected outcome (and provide an explanation on how you arrive at the values, for the first one or two - walk us through the logic and process using the sample data provided).

 

[Jason1973]

no worries and sorry.
I would like to add the 2.00 (see below) to the overall tracker count for example 'Pricing and costing', which I I use a 'countif' function where I can only count 1.00 but for the same job there is 2 jobs with the same title and grade e.g. Pricing and Costing so I have been asked to insert '2.00' but I want that to add to the overall tally in the tracker. In the formula below I insert '+1' at the end but I prefer to have a formula where I don't need to manually insert that additional part of the formula.

Here is the formula that I use in the 'overall tracker'
=SUM(COUNTIF('14th May 2021'!E$5:E$24,'Data & Links'!C$13))+1

Band:	Role:	Ref:	Team:	FTC Permanent Secondment (specify WTE):	Closing Date	Link: e.g. (EoI, Trac or jobs.nhs.uk)	WTE
9	Senior Analytics Manager, Service Transformation	1643	Specialised Commissioning	FTC/Secondment	14/05/2021	Link	1.00
8d	National Programme of Care Senior Manager (Internal Medicine)	990-1-EI4947-CE	Specialised Commissioning	Permanent	16/05/2021	Link	1.00
8d	Senior Scientific Advisor	990-1-EI4961-CE	Specialised Commissioning	Permanent	19/05/2021	Link	1.00
8d	National Programme of Care Senior Manager (Trauma)	1644	Specialised Commissioning	FTC/Secondment	21/05/2021	Link	1.00
8c	Delivery Lead (Senior Programme Manager)	1567	Specialised Commissioning	FTC/Secondment	14/05/2021	Link	1.00
8c	Senior Financial Planning Lead	990-2-EI4788-CE	Finance Planning and Delivery	Permanent	16/05/2021	Link	1.00
8c	Medicines Policy Lead	1665	Commericial Medicine Devices/Unit	FTC/Secondment	19/05/2021	Link	1.00
8b	Senior Analytical Manager	990-1-EI4925-MI	CDAO	Permanent	16/05/2021	Link	1.00
8a	Finance Planning Manager	990-2-EI4900-CE	Finance Planning and Delivery	Permanent	23/05/2021	Link	1.00
8a	Financial Planning Manager	1542	Finance Planning and Delivery	FTC/Secondment	21/05/2021	Link	1.00
7	Analyst	990-1-EI4963-SE	CDAO	Permanent	18/05/2021	Link	1.00
7	Genomics Governance Support Officer	990-1-EI4743-CE-A	Specialised Commissioning	Permanent	18/05/2021	Link	1.00
7	Analyst	1585	Strategic Financial Planning	Permanent	17/05/2021	Link	1.00
6	Payment Development Officer	990-1-EI4946-LN	Pricing and Costing	Permanent	16/05/2021	Link	2.00
6	Analytical Officer	990-1-EI4962-SE	CDAO	Permanent	18/05/2021	Link	1.00
6	Payment Development Officer	990-1-EI4946-LN	Pricing and Costing	Permanent	16/05/2021	Link	1.00
6	Analytical Officer	990-1-EI4962-SE	CDAO	Permanent	18/05/2021	Link	1.00
4	Business Support Assistant	1648	Specialised Commissioning	Permanent	21/05/2021	Link	1.00

 

[Joe4]

OK, you seem to be repeated what you already said, and really not answering the questions I asked. I really need to know where everything exists. Your formulas list ranges, but I have no way of knowing where that resides on your sheet. So please answer each of the following questions:

1. The sample data you posted, what sheet is that from?

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?
And what column letter in "WTE" located in?

3. The formula you posted, is that formula what is currently in your "WTE" column?

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?

 

[Jason1973]

thank you for getting back to me.
The cell for banding is in 'B18' and 'I18' I want the overall tracker to add that column.
Added the overall tracker below (using the XL2BB)

Vacancies - Finance Directorate .xlsx
	A
1
Tracker

Please let me know if you are able to see the table?

 

[Joe4]

thank you for getting back to me.
The cell for banding is in 'B18' and 'I18' I want the overall tracker to add that column.
Added the overall tracker below (using the XL2BB)

Vacancies - Finance Directorate .xlsx
	A
1
Tracker

Please let me know if you are able to see the table?

No, that did not appear to work out. Note that there is a Test Here forum that you can use to test out the posting tools before using them in your actual question.

Also, you still have not answered all my questions (you seem to only answer a few tiny bits here-and-there).
If you would like me to help you, please address each and every question, listed 1-4 in my previous post.

 

[RoryA]

Perhaps you want SUMIF, not COUNTIF. Not really clear to me either though.

 

[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 'I18' column' that is numerical and add to the overall tracker.
And what column letter in "WTE" located in?

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

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