# Scatter Plot X,Y Coordinate Number of Occurrences

#### spochedly

##### New Member
I am looking to build a scatter plot to illustrate how many times an X,Y coordinate appears. So, if I have 15 occurrences of 2,3 then I would like to see 15 points at 2,3 on my chart. If I have 3 occurrences of 2,1 XY corrdinate, I want to see 3 points right there.

I need this to be a scatter plot to best illustrate the distribution of data.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Jon Peltier

##### MrExcel MVP
How will I know that there are 15 points at 2,3?

Maybe instead of stacking a lot of points on top of each other, where you can't see them, you might make a marker proportional to the number of times the X,Y coordinates appear. That would be a bubble chart.

So we need to count the occurrence of XY pairs. In my sample here, I have X in B3:B28 and Y in C3:C28. I have listed the possible combinations of X and Y in E3:F18. In G3 (filled down to G18) I have the formula

=COUNTIFS(\$B\$3:\$B\$28,E3,\$C\$3:\$C\$28,F3)

So I have a count for each combination of X and Y.

And I made a bubble chart from this data.