I have 2 worksheets in Excel:
Plot
Waypoint
HbjM7.png
How would I search column D in the Waypoint table to count where the plot+region (i.e. MK1, MK2...) matches the postcode and where the sequence is equal to 1.
This is the formula I have so far:
=SEARCH(CONCATENATE(B:B,"",A:A),Waypoint!D:D,"0")
but it keeps returning #VALUE in the Plot table.
I want it to concatenate whatever is in plot and region, then check column D in Waypoint to see if there are any matches, if there is a match is the value in sequence = 1, if so then count it... (sorry if this pseudocode is not clear enough)
e.g. in the example above
For plot 1, there are 2 matches so jobCount = 2
For plot 2, there is 1 match so jobCount = 1
For plot 4 there are 1 matches so jobCount = 1
I hope it makes sense, I've tried very hard to be clear!
Bookmarks