Tallying matching cells based on multiple criteria.

4rcanine

New Member
I've run into a problem that's above my ability to solve

Within my worksheet, I have a table full of logistics orders.

In column F is the Submission Date of each order and in column I is the priority of that order.
My sheet currently highlights the order dates in column F into three categories - under 30 days, over 30 days and over 60 days. A colour is then applied to each cell in column F based on how long that order has been sitting there for.
Column I contains the priorities Stock, Routine, Urgent and Immediate.

I've been able to make tallies of each category in column F using an extended version of the following:

completedcount = 0

Dim lngLstCol As Long, lngLstRow As Long
lngLstRow = Sheets("MasterList").UsedRange.Rows.Count
For Each cell In Sheets("MasterList").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 4 Then
completedcount = completedcount + 1

From there, the value completedcount is placed into a cell on another worksheet.

What I'm now looking to do is make a tally based on both columns, so that I have a seperate count of all orders that are routine, stock, urgent and immediate that have also been sitting for at least 30 days - giving me 4 different numbers to work with per category.

I believe this is a possibility but the code required to do this is above me. I've done a heap of searching but have not been able to find anything similar.
Apologies if this is difficult to understand.. A little difficult to explain ><

Any help is greatly appreciated!

Cheers

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Windows XP, Excel 2003.

Noone up to the challenge? :P

4rcanine,

can you give me 24hrs to have a look at it?

Do you still want to work off the cell colour or can we use your values of - "routine, stock, urgent and immediate".

I am thinking of a series of 'if statements'..

If cell value= "routine" then routine= routine+1
If cell value= "stock" then stock = stock +1
etc
.

Where do you want these counts put?

FarmerScott

4rcanine,

can you give me 24hrs to have a look at it?

Do you still want to work off the cell colour or can we use your values of - "routine, stock, urgent and immediate".

I am thinking of a series of 'if statements'..

If cell value= "routine" then routine= routine+1
If cell value= "stock" then stock = stock +1
etc
.

Where do you want these counts put?

FarmerScott

Yeah mate, take your time on this one.

I have a 'Tracking' tab that contains hidden counters that a couple of graphs use. Here's a picture:
http://imageshack.com/a/img537/33/ixWdzg.jpg

That data is pulled off the next tab, which contains all of my orders.
The code I have doing that is as follows:
Dim cell As Range
completedcount = 0
cancelledcount = 0
investigationcount = 0
under30 = 0
above30 = 0
above60 = 0
above90 = 0
totalcount = 0

For Each cell In Sheets("SQN").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 4 Then
completedcount = completedcount + 1
ElseIf cell.Interior.ColorIndex = 3 Then
cancelledcount = cancelledcount + 1
ElseIf cell.Interior.ColorIndex = 8 Then
above30 = above30 + 1
ElseIf cell.Interior.ColorIndex = 6 Then
above60 = above60 + 1
ElseIf cell.Interior.ColorIndex = 45 Then
above90 = above90 + 1
ElseIf cell.Interior.ColorIndex = 39 Then
investigationcount = investigationcount + 1
End If

Next cell

totalcount = completedcount + cancelledcount + investigationcount + above30 + above60 + above90
under30 = lngLstRow - totalcount - 1

totalclosedcount = completedcount + cancelledcount

Worksheets("Tracking").Range("A51") = totalclosedcount
Worksheets("Tracking").Range("B51") = cancelledcount
Worksheets("Tracking").Range("C51") = investigationcount
Worksheets("Tracking").Range("D51") = under30
Worksheets("Tracking").Range("E51") = above30
Worksheets("Tracking").Range("F51") = above60
Worksheets("Tracking").Range("G51") = above90

However what I'd like to do now is have a third graph that shows (for example) how many orders that have sat for more than 90 days are urgent, routine, immediate or stock.

So for example, if ColorIndex = 45 AND Column I = 'ROUTINE', add 1 to '90daysroutine' counter.
Sadly I don't have the knowledge to be able to make this work though.

Have you considered using the SUMPRODUCT() function instead of VBA?

Have you considered using the SUMPRODUCT() function instead of VBA?

That came up while I was investigating other ways I could do this. I believe the problem I found was that the range I'm tallying from is constantly changing; the columns we're looking at can one day be 1:25 and the next be 1:300, so it was too hard to do outside VBA.

Incorporate this into your code where it best fits-

Code:
`````` ninetydaysurgentcount = 0
ninetydaysroutinecount = 0
ninetydaysimmediatecount = 0
ninetydaysstockcount = 0

For Each cell In Sheets("SQN").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 45 And cell.Offset(0, 3) = "URGENT" Then
ninetydaysurgentcount = ninetydaysurgentcount + 1
End If
For Each cell In Sheets("SQN").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 45 And cell.Offset(0, 3) = "ROUTINE" Then
ninetydaysroutinecount = ninetydaysroutinecount + 1
End If
For Each cell In Sheets("SQN").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 45 And cell.Offset(0, 3) = "IMMEDIATE" Then
ninetydaysimmediatecount = ninetydaysimmediatecount + 1
End If
For Each cell In Sheets("SQN").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 45 And cell.Offset(0, 3) = "STOCK" Then
ninetydaysstockcount = ninetydaysstockcount + 1
End If
Next cell``````

Hope that helps,

FarmerScott

Incorporate this into your code where it best fits-

Code:
`````` ninetydaysurgentcount = 0
ninetydaysroutinecount = 0
ninetydaysimmediatecount = 0
ninetydaysstockcount = 0

For Each cell In Sheets("SQN").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 45 And cell.Offset(0, 3) = "URGENT" Then
ninetydaysurgentcount = ninetydaysurgentcount + 1
End If
For Each cell In Sheets("SQN").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 45 And cell.Offset(0, 3) = "ROUTINE" Then
ninetydaysroutinecount = ninetydaysroutinecount + 1
End If
For Each cell In Sheets("SQN").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 45 And cell.Offset(0, 3) = "IMMEDIATE" Then
ninetydaysimmediatecount = ninetydaysimmediatecount + 1
End If
For Each cell In Sheets("SQN").Range("F2:F" & lngLstRow)
If cell.Interior.ColorIndex = 45 And cell.Offset(0, 3) = "STOCK" Then
ninetydaysstockcount = ninetydaysstockcount + 1
End If
Next cell``````

Hope that helps,

FarmerScott

Man, I could kiss you.
Works perfectly. Thankyou so much!

I wasn't aware the Offset feature even existed - I think I'll now be incorporating it into a few things.

Cheers!

Replies
10
Views
235
Replies
11
Views
564
Replies
2
Views
308
Replies
0
Views
379
Replies
4
Views
263

1,216,099
Messages
6,128,823
Members
449,470
Latest member
Subhash Chand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back