Hi,
I am trying to come up with a simple formula to write on cell C2 that I can drag that counts how many occurences of a specific university appear in the cells E2 to DA100000 on sheet1.
The thing is that I could easily calculate using an array with a SUM(IF) but this takes lots of resources. My excel even crashes. The other way is to create an extra column in sheet1 that concatenates all cells E2->DA2, then drag formula to reach 100000 (I'll have 100000 respondents) but this results in an increase to 15MB just by doing this.
Another option is to place countifs manually choosing the appropriate column per country. The problem here is that this isn't very dynamic. Every time I add an University I'll need to do the same countif for the corresponding country. I'd prefer just drag a formula and find the correct column.
Any suggestions?
Vasco
Bookmarks