Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 enter image description here

share|improve this question
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
up vote 4 down vote accepted

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...


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

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.