Calculating which state a project came from using a 2 digit legend

[matrixpom]

Hey guys,

I have a workbook the logs projects that were completed in a specific year, I recently had help from this forum to calculate how many projects were completed in a particular year ignoring multiple entries of the same project number. Now I need to calculate where in Australia did each project come from? Each project starts with a specific 2 digits e.g. 1500012345 would be 15 = Sydney & 250003214 would be 25 = Brisbane, ultimately I want to produce a map chart to show the number of projects completed state by state in a given year. I have an example attached, to explain further...as always guys, any assistance would be greatly appreciated ok - Marc

[Bo_Ry]

The data in column B has Text and number.
Excel considers text "1" and number 1 differently.

G2
=COUNT(1/FREQUENCY(IF(YEAR($C$2:$C$3004)=G1,--$B$2:$B$3004),--$B$2:$B$3004))

P2
=COUNT(1/FREQUENCY(IF(YEAR($C$2:$C$3004)=P$1,IF(ISNUMBER(MATCH(LEFT($B$2:$B$3004,2),IF($H$7:$H$19=$O2,$G$7:$G$19&""),)),--$B$2:$B$3004)),--$B$2:$B$3004))


For MS365
G2
=COUNT(UNIQUE(FILTER(--$B$2:$B$3000,YEAR($C$2:$C$3000)=G1)))

P2
=COUNT(UNIQUE(FILTER(--$B$2:$B$3000,(YEAR($C$2:$C$3000)=P$1)*ISNUMBER(MATCH(LEFT($B$2:$B$3000,2),FILTER($G$7:$G$19&"",$H$7:$H$19=$O2),)))))

[MarvinP]

Hi Matrixpom,

Your problem looks like a Pivot Table answer to me. See my answer attached.
Pivot Count Year Columns.xlsx

[matrixpom]

Thanks again Bo_Ry, I intend to tidy up the data in column B to reflect only numbers, so your formula works a treat as always! This is perfect & I like the update to your first formula for finding unique numbers! Thanks mate & have a great weekend - Marc

[matrixpom]

Hi MarvinP,
Thanks for the link, I need to research pivot tables a lot more, thanks for the link - Marc