Countifs problem

**cegs0007** · 03-27-2018, 01:01 AM

Hi,
I've not really used much excel for the past 10 years - used to be quite good with it but as the old saying goes - "use it or lose it". Anyway, hopefully somebody here can help

21/03/18 22/03/18 23/03/18 24/03/18 25/03/18 26/03/18 27/03/18 28/03/18 No of days (What i need to get out of excel)

Andy FIA D D D D D D FOP 7
Barnie FIP D D D D 4
Charlie D D D D D FOA 5
Dexter DIA D DOP DIP D 3

Start Date End Date
21/03/18 27/03/18

So basically I have a roster and want to count for each person (No of days column) the number of days they have worked in a given time period (start/end date)- need the FIA/FIP/DIA/DIP/D codes but not FOA or FOP

I got as far as using a countif statement to count the FIA + D etc... for an entire row but can't work out how to limit it to the date range
=(COUNTIF(I10:XFD10,"FIA")+COUNTIF(I10:XFD10,"FIP")+COUNTIF(I10:XFD10,"DIA")+COUNTIF(I10:XFD10,"DIP")+COUNTIF(I10:XFD10,"D"))

Hopefully this makes sense to someone, would really appreciate some help. Thanks.

**AliGW** · 03-27-2018, 01:12 AM

You probably need COUNTIFS, not COUNTIF.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**kersplash** · 03-27-2018, 01:13 AM

You'll be better attaching a sample workbook with what you are trying to achieve.

Go Advanced -> Manage Attachments -> Upload

**shukla.ankur281190** · 03-27-2018, 01:15 AM

Try

J2

Formula:

Please Login or Register  to view this content.

Copy down

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H	I	J
1		21.03.2018	22.03.2018	23.03.2018	24.03.2018	25.03.2018	26.03.2018	27.03.2018	28.03.2018	Result
2	Andy	FIA	D	D	D	D	D	D	FOP	7
3	Barnie	FIP	D	D	D	D				5
4	Charlie	D	D	D	D	D	FOA			5
5	Dexter	DIA	D	DOP	DIP	D				4
6
7		Start Date	End Date
8		21.03.2018	27.03.2018

Sheet: Sheet1

**cegs0007** · 03-27-2018, 01:35 AM

Thanks for all the quick replies.
shukla.ankur281190 that's exactly what I'm after. I've applied the formula to my spreadsheet but it's giving me the #value error. Does the date have to be in written in a specific way for it work?

**shukla.ankur281190** · 03-27-2018, 01:45 AM

No need any specific date, system date is sufficient. # Value error means Incorrent Data Types. Press Alt-T-U-F and slowly press enter and see when error come? If you found when error come you can speak me or if you are unable to do process attach the sample spreadsheet for examine why it is happening

**cegs0007** · 03-27-2018, 02:43 AM

Hey Shukla,
Have attached as I still can't figure it out sorry.

**finalazy** · 03-27-2018, 02:49 AM

Change the last part of the formula. Your range did not tally so it is showing error. Change G6:XFD6 to G6:GCK6.

**cegs0007** · 03-27-2018, 02:53 AM

Thanks for you help Shukla and Finalazy - works perfectly now. Rep added.

Countifs problem

LinkBack

Thread Tools

Rate This Thread

Display