# How to plot coordinates in Excel?

I have a list of goals scored by Ronaldo and Messi and have created a manual heat map of where they have scored goals from. I want to write coordinates in a cell in a list and for the heat map to create itself rather than me having to manually input the number in the cell.

The goal list

1 | vs Barcelona | "running total" | Date | Coordinates
2 | vs Getafe | "running total" | Date | Coordinates
3 | vs Galatasary | "running total" | Date | Coordinates

etc etc and the information in the coordinates cell will place a number 1 at the relevant cell on another page that has the layout of a pitch on it. Any conflicting Coordinates in the list will cumulate in the cell.

What (if such a formula exists) would i put in the Coordinates cell in the goal list?

The heatmap looks like this

-
It would help to know what your other sheet is supposed to look like, but from what I'm reading it sounds like you should just do away with the coordinates and use `VLOOKUP` on the other sheet. – druciferre Sep 22 '13 at 23:31
Yes, I think doing the other way around might be easier ... – Aasim Azam Sep 22 '13 at 23:33

## 1 Answer

In your other sheet, you can use a `SUMIF` formula to lookup the running totals by the coordinate and add them together from the first sheet... It would look something like this...

``````=SUMIF(\$E\$1:\$E\$3,"Coordinates",\$C\$1:\$C\$3)
``````

To acheive the colorization, you can use Conditional Formatting to check the value of the cell and set the background color.

-
please have a look at the picture I updated of what the heat map looks like – Aasim Azam Sep 23 '13 at 0:03
Vlookup wasn't working, I've used countif function – Aasim Azam Sep 23 '13 at 0:16
@AasimAzam, after looking at your picture more closely, I realized it would be better to use `SUMIF`. `SUMIF` will add all of the matched totals together. – druciferre Sep 23 '13 at 0:21