LOOP with COUNTIFS MULTI CRITIERIA

[NorthbyNorthwest]

Hi, everyone. Need help with task. I have a two tables (list objects) in a workbook. Table 1 has a list of assignments (100- 150) similar to :

List ID	Begin Date	End Date	Count	Assigned on	Assigned to	# Pending	Status	Status Date
L0001A	05/24/14	06/06/14	275	03/16/15	Emp 1	1	IP	03/23/16
L0002A	08/16/14	08/26/14	256	03/16/15	Emp 2	32	RE	03/23/16
L0002R	08/16/14	08/26/14	32	03/03/16	Emp 3	1	IP	03/23/16
L0003A	09/17/14	09/24/14	259	03/16/15	Emp 4	0	CL	03/03/16
L0004A	10/31/14	11/06/14	262	05/05/15	Emp 6	1	IP	03/23/16
L0005A	01/05/15	01/07/15	236	06/02/15	Emp 7	1	IP	03/23/16
L0006A	01/08/15	01/12/15	269	06/02/15	Emp 8	1	IP	03/23/16
L0007A	01/13/15	01/15/15	243	06/02/15	Emp 9	3	IP	03/23/16
L0008A	01/21/15	01/23/15	234	06/16/15	Emp 10	2	IP	03/23/16
L0009A	01/24/15	01/28/15	280	06/16/15	Emp 11	8	RE	03/03/16
L0009R	01/24/15	01/28/15	8	03/03/16	Emp 12	2	IP	03/23/16
L0010A	01/29/15	02/02/15	286	06/16/15	Emp 13	12	CL	03/23/16

<tbody>
</tbody>

Table 2 is aw weekly report with current inventory of open cases that make up lists. Runs between 3,000 and 5,000. Structured like so:

File Num	Last Name	File Date	Client ID	Caseworker
123	SMITH	05/28/14	AAA	Employee 1
456	JONES	08/22/14	BBB	Employee 2
789	BROWN	09/18/14	CCC	Employee 3
122	WHITE	10/31/14	ABC	Employee 4
455	WILLIAMS	01/05/15	DEF	Employee 5
788	HILL	01/08/15	GHI	Employee 6
721	GREEN	01/13/15	DDD	Employee 7
358	JOHNSON	01/14/15	EEE	Employee 8
597	THOMAS	01/14/15	FFF	Employee 9
721	CARTER	01/23/15	GGG	Employee 10
333	ADAMS	01/23/15	HHH	Employee 11
237	WASHINGTON	01/25/15	JKL	Employee 7
358	ROBINSON	01/28/15	MNO	Employee 2
252	BROOKS	01/29/15	PQR	Employee 3
987	HARRIS	01/29/15	III	Employee 1

<tbody>
</tbody>

I want to update the # pending in column 7 of Table 1 using code that would first look at status in column 8 of Table 1. If status is RE or CL, I don't want anything to happen. I want data in cell to remain as is. If, however, the status in column 8 is IP, I want to count the number of cases on the weekly update (Table 2) based on the date ranges (begin and end dates) in Table 1 and file date on weekly report. I was unsuccessful in coming up with a formula so now I'm trying code and struggling. I'm trying to use a for-next loop and worksheetfunction.countifs. See code below. Any help anyone can offer would be greatly appreciated.

Sub CountRemainder()
 
Dim i As Integer
Dim FinalRow As Long
 
 
FinalRow = ThisWorkbook.Worksheets("List").Range("A" & Rows.Count).End(xlUp).Row
 
For i = 6 To FinalRow
 
    If ThisWorkbook.Worksheets("List").Range.Cells(i, 8).Value <> "IP" Then
    'do nothing
 
    Else
    On Error Resume Next
 ThisWorkbook.Worksheets("List").Range.Cells(i, 7).Value = Application.WorksheetFunction.CountIfs(ThisWorkbook.Worksheets("Cases").Range("C5:C"), ">=" & _
 ThisWorkbook.Worksheets("List").Range.Cells(i, 2), ThisWorkbook.Worksheets("Cases").Range("C5:C"), "<=" & ThisWorkbook.Worksheets("List").Range.Cells(i, 3))
End If
Next i
End Sub

 

[Peter_SSs]

I'm struggling to understand the actual calculation required. Can you step us through, with words, a couple of the sample rows of how you would do this manually so we can see what the results for those should be and how those results would be obtained.

 

[dhreejworld]

I think it can be done with a formula, I have wrote it on sample sheet I made with given data. I just joined don't know how to attach a sheet here.

 

[dhreejworld]

Use below formula in column J of List sheet.
I changed "Assigned to" field, Emp 1 = Employee 1 (same as case sheet)
=IF(H2="IP",SUMPRODUCT((F2=Cases!$E$2:$E$16)*(Cases!$C$2:$C$16>=B2)*(Cases!$C$2:$C$16<=C2)),G2)

 

[Peter_SSs]

I think it can be done with a formula, I have wrote it on sample sheet I made with given data. I just joined don't know how to attach a sheet here.

Welcome to the MrExcel board!

You cannot attach actual files in this forum. I suggest that you have a look at the 'Forum Rules' and the 'Forum Use Guidelines' (links to both in my signature block below) as there is considerable useful information there about how to post sample data and what you should & shouldn't do in that regard.

 

[NorthbyNorthwest]

Hi, Pete. In the list assignment table (first table), first row you'll see that Employee 1 was assigned a list of 275 cases, ordered by our customers on dates 5/24/14 to 6/6/14. I run a report weekly which lists all open cases in my office's inventory. The list will show the order date. That is the date column (col 3) in second table. I want a count of how many of the original 275 cases my employee has yet to close. So right now I drag my mouse down column to select all cases in the date range and look at bottom right corner of computer to get the count. Example: The count might be 175 cases pending in our inventory for dates 5/24/14 to 6/6/14. I then enter that number in the # pending column in table 1. Because the inventory I'm dealing with is in the thousands, you can imagine it takes me a while to fill in the entire column, which is why I was trying to automate the process. Again, I update the #pending column weekly, so eventually the original 275 should drop to zero.

There is only one catch or caveat to what I want to do, and that is the status column in the list assignment table. If the status is not IP (which stands for in process) then I want to skip the row. The other two possible statuses are RE for reassignment and CL for closed. If the list has been reassigned or closed I don't need an updated count.

 

[Peter_SSs]

Try this in a copy of your workbook.
Check the Sheet name, Table names & Table headers

Sub UpdatePending()
  With Sheets("List").Range("Table1['# Pending]")
    .Parent.Activate
    .Value = Evaluate("if(Table1[Status]=""IP"",countifs(Table2[File Date],"">=""&Table1[Begin Date],Table2[File Date],""<=""&Table1[End Date])," & .Address & ")")
  End With
End Sub

 

[NorthbyNorthwest]

Hi, Peter. I cannot thank you enough for the help. This bit of code will really be a lifesaver for me. And thanks for introducing to Evaluate. I wasn't familiar with it. So I did a little research trying to understand your beautifully simple but powerful code. I will try in the future using evaluate on my own. I also liked that your code addressed the fact that I was referencing list objects. I use tables a lot because of the many advantages that come with them but then find myself unsure on how to write code involving them. Sometimes writing code for them just as I would for a normal range works, and sometimes it doesn't. As I've said before, this forum rocks! Peter, have an awesome week Down Under.

 

[Peter_SSs]

You are very welcome, and thank you for your generous comments.