COUNTIF: 2 criteria: Month Column & Text Column

[maychui]

Hi all,

I am currently working on a turnover report and i am having some problems using the countif formula to count e.g. the number of staff resigning on a certain month with a certain title.[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Title[/TD]
[TD]Last day of work[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sales Associate[/TD]
[TD]25-Jan-15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sales Associate[/TD]
[TD]16-Apr-15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Senior Sales[/TD]
[TD]27-Jan-15[/TD]
[/TR]
</tbody>[/TABLE]
I tried to search online and found that I will have to use SUMPRODUCT instead of COUNTIF for this matter as there are the month column and text column. However, I have not too familiar with this function.
Can anyone help so that i can have the result of:
in January, there are 1 Sales Associate leaver and 1 Senior Sales leaver and 1 Sales Associate leaver in Apr

Note that I only look at the month instead of the date.
thanks for your help in advance

 

[Trevor G]

Try this

Excel Workbook
	A	B	C	D	E	F
1	1	Title	Last day of work	Title	01/01/2015	31/01/2015
2	2	Sales Associate	25-Jan-15	Sales Associate	1
3	3	Sales Associate	16-Apr-15	Senior Sales	1
4	4	Senior Sales	27-Jan-15
Sheet2

 

[maychui]

Thanks for your reply. but is there a method for this that i don't need to add the E1 &F1 cell? Since my report will be showing data from January to December , there will be too much information. Is it possible to simply use the month in column c to create the formula?

 

[maychui]

I have actually tried this: (using the table in the 1st post)
=SUMPRODUCT(--(B2:B4<>""),--(MONTH(B2:B4)=6))

It works fine and comes up with the right answer but if i want to know the separate turnover by position i tried to edit the formula to
=SUMPRODUCT(--(B2:C4<>""),--(MONTH(B2:B4)=6)),--(A2:A4="Sales Associate")

However it comes up with the VALUE error. anyone know how i can edit the formula to make it work?

 

[skywriter]

Cross Posted