Ottsel
Board Regular
- Joined
- Jun 4, 2022
- Messages
- 174
- Office Version
- 365
- Platform
- Windows
I have this custom function I'm working on. the setup is as follows:
The sheet its referencing is "TRACKER". I want to be able to reference B2 or any cell really as the main search. In this example on the TRACKER sheet B2, B6, B7, and B8 are whats in being looked for. If whats in Column O equals AWARDED, then count if the other requires are met: J must equal the year that's selected, which in this example is 2023 and whats in column C must be unique. If whats in column C occurs again and the value changes in column O it needs to update the count. So, in this example below row 2's status is AWARDED, so +1 to the overall count, but later its changed in row 7 from AWARDED to LOST, so the count would change. So, with this example the answer should equal: 1, since only 1 was truly AWARDED. Since row 1 was in 2022 it won't be picked up in the count. Any help or direction regarding this would be highly appreciated! Also, if any form of the word "LOST" is located it would be great to have that included. For some reason people tend to put "ASSUMED LOST" even though its been noted to avoid that.
Excel Formula:
=CountAwards(B2, 2023)The sheet its referencing is "TRACKER". I want to be able to reference B2 or any cell really as the main search. In this example on the TRACKER sheet B2, B6, B7, and B8 are whats in being looked for. If whats in Column O equals AWARDED, then count if the other requires are met: J must equal the year that's selected, which in this example is 2023 and whats in column C must be unique. If whats in column C occurs again and the value changes in column O it needs to update the count. So, in this example below row 2's status is AWARDED, so +1 to the overall count, but later its changed in row 7 from AWARDED to LOST, so the count would change. So, with this example the answer should equal: 1, since only 1 was truly AWARDED. Since row 1 was in 2022 it won't be picked up in the count. Any help or direction regarding this would be highly appreciated! Also, if any form of the word "LOST" is located it would be great to have that included. For some reason people tend to put "ASSUMED LOST" even though its been noted to avoid that.
| Book1 | |||||
|---|---|---|---|---|---|
| B | C | D | |||
| 2 | abc | #NAME? | |||
Sheet2 | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2 | D2 | =CountAwarded(B2,2023) |
| Book1 | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | abc | place 0 | 12/20/2022 | AWARDED | |||||||||||||
| 2 | abc | PLACE 1 | 9/5/2023 | AWARDED | |||||||||||||
| 3 | def | VILLAGE 1 | 9/8/2023 | ASSUMED LOST | |||||||||||||
| 4 | def | VILLAGE 2 | 9/13/2023 | AWARDED | |||||||||||||
| 5 | def | VILLAGE 2 | 9/15/2023 | LOST | |||||||||||||
| 6 | abc | place 2 | 9/18/2023 | LOST | |||||||||||||
| 7 | abc | place 1 | 9/20/2023 | LOST | |||||||||||||
| 8 | abc | place 4 | 10/1/2023 | AWARDED | |||||||||||||
TRACKER | |||||||||||||||||
VBA Code:
Function CountAwards(lookupValue As Range, year As Integer) As String
Dim ws As Worksheet
Dim count As Integer
Dim cell As Range
Dim lastRow As Long
Set ws = Worksheets("TRACKER")
lastRow = ws.Cells(ws.Rows.count, "B").End(xlUp).Row
count = 0
For Each cell In ws.Range("B2:B" & lastRow)
If cell.Value = lookupValue.Value Then
If cell.Offset(0, 12).Value = "AWARDED" And year(cell.Offset(0, 9).Value) = year Then
If Application.WorksheetFunction.CountIf(ws.Range("C2:C" & lastRow), cell.Offset(0, 1).Value) = 1 Then
count = count + 1
ElseIf Application.WorksheetFunction.CountIfs(ws.Range("C2:C" & lastRow), cell.Offset(0, 1).Value, ws.Range("O2:O" & lastRow), "LOST") > 0 Then
count = count - 1
End If
End If
End If
Next cell
CountAwards = count
End Function
