Tallying matching cells based on multiple criteria.

[4rcanine]

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

 

[farmerscott]

Thanks for the "man love".The 'offset' is very handy particularly with 'resize'. so for example, if you wanted to extract data one row above and below "Urgent". You would use cell.offset(-1,0).resize(3,0). So the offset basically moves the cell up one (you could say it is the new active cell) and then the resize highlights a 3 by 1 cell range for you to do whatever.cheersFarmerScott

 

[4rcanine]

Awesome! You just answered my next question haha.

Cheers

 

[FDibbins]

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.

As with the VBA solution, OFFSET() is a regular excel function that can be built into various formulas, including sumproduct. But you have the answer you wanted, so thats great

 

[farmerscott]

Actually, the resize in my last post should have read resize(3,1) not resize (3,0). You can't have a selection of 0 columns.

FS