COUNTIF assistance

[Mikec1012]

Hello everyone! I currently use the following formula:

=COUNTIFS('CLEX01 '!$K:$K,'TEST REPORT'!H5,'CLEX01 '!$A:$A,'TEST REPORT'!H4)

Column 'CLEX01 '!$K:$K - it's the day something was completed
Cell 'TEST REPORT'!H5 - a cell where a user can change the date to generate a report
Column 'CLEX01 '!$A:$A - has a list of employee names
Cell 'TEST REPORT'!H4 - a cell where the employee name can be changed

I am using this in 3 sections. First section, it does everything fine.

2nd Section - I need to tweak the formula to now give a Month To Date report based off the MONTH in Cell H5. How would I adjust the formula?

3rd section - Similar to section 2 but I would simply search for a COUNTIF based off a months value for all employees. However, it would still need to reference Cell H5 to pull the month.

Any help would be awesome.

 

[estevaoba]

Try this for 2nd section:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=SUMPRODUCT(('[COLOR=#333333]CLEX01 '!$A:$A[/COLOR]=[COLOR=#333333]'TEST REPORT'!H4[/COLOR])*(MONTH([COLOR=#333333]'CLEX01 '!$K:$K[/COLOR])=MONTH([COLOR=#333333]'TEST REPORT'!H5[/COLOR])))[/TD]
[/TR]
</tbody>[/TABLE]

And this for 3rd:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=SUMPRODUCT((MONTH([COLOR=#333333]'CLEX01 '!$K:$K[/COLOR])=MONTH([COLOR=#333333]'TEST REPORT'!H5[/COLOR]))*1)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mikec1012]

I tried copying your formulas into my file and I get the #VALUE ! error in the cell. I did go back and double checked to ensure the reference was correct while using your base formula.

 

[AhoyNC]

Here is an option using COUNTIFS.
Change ranges to match your data.

Excel Workbook
	A	B	C	G	H
1	Names	Date
2	Name1	9/12/2017
3	Name2	10/2/2017
4	Name1	10/5/2017		Name	Name1
5	Name4	9/8/2017		Report Date	9/1/2017
6	Name1	9/16/2017
7	Name6	9/17/2017
8	Name7	10/2/2017		Month Count	3
9	Name1	9/22/2017
10	Name9	10/5/2017		All Emp. Month	5
11	Name10	10/28/2017
Sheet

 

[Mikec1012]

Ok so I am working with adjusting my formula for my cell. I have the H8 one working but when I change my Report Date to say 09/08/2017 - it will then edit the month count. I ideally just need it to search 2 columns based off Employee Name and Date as long as it falls within that month. I will go back and ensure I copied correctly.

 

[estevaoba]

I don't get it. Sumroduct works in my simulation here.

But if AhoyNC's formula works, fine. But it works better like this for H8:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=COUNTIFS($A$2:$A$151,$H$4,$K$2:$K$151,">="&DATE(YEAR(H5),MONTH($H$5),1),$K$2:$K$151,"<="&EOMONTH($H$5,0))[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mikec1012]

Thank you for pointing me in the right direction. Did my reply make sense?

Your first formula - I tweaked it to this:
=COUNTIFS('CLEX01 '!A:A,C4,'CLEX01 '!K:K,">="&C5,'CLEX01 '!K:K,"<="&EOMONTH(C5,0))

A:A - reference to employee name
C4 - employee name
K:K - date work was done
C5 - date that the user can enter

C5 can be anything from 10/01/2017 to 11/15/2017 - any date.

However, when I change the date to modify the daily report - it changes the values of the other two formulas.

I need the 2nd formula to search my data and tally for specific employees and then based off the month.

The 3rd box... I need to just search all of the data based off the month in regards to date. Employee is of no concern. I just need to count the column and anything that falls from the 1st of the month to the end of the month will be accounted for and does not change.

Hope this isn't confusing.

 

[AhoyNC]

See if this works for you.
You will need to change ranges and sheet names to match your data.

As pointed out by estevoba, since your dates in C5 can be any date and not just first of the month the formulas I gave you needed to be changed to find the first day of the month for monthly figures.

Excel Workbook
	A	B	C	D	E	J	K
1	Names						Date
2	Name1						9/12/2017
3	Name2		Find				10/2/2017
4	Name1		Name1				10/5/2017
5	Name4		9/12/2017				9/8/2017
6	Name1						9/12/2017
7	Name6		2	Day Count for Emp.			9/17/2017
8	Name7		3	Month Count for Emp.			10/2/2017
9	Name1		5	Month Count All Emp.			9/2/2017
10	Name9						10/5/2017
11	Name10						10/28/2017
Sheet