Tallying matching cells based on multiple criteria.

4rcanine

New Member
Joined
Sep 2, 2014
Messages
25
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Sep 2, 2014
Messages
25
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.

Thanks for your help!
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

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

4rcanine

New Member
Joined
Sep 2, 2014
Messages
25
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.
 

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
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
 

4rcanine

New Member
Joined
Sep 2, 2014
Messages
25
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top