consolidating data

If I had to do this job, I would export the spreadsheet in Excel-tab format, read it with a programming language that has dictionaries, add a key corresponding to this pair, and keep updating the value corresponding to each key as the count for this pair increases. Programming languages that would do well for this kind of task include Python, Perl, Ruby, and Visual Basic. (With Visual Basic, you probably wouldn't even have to do the Excel-tab export.) Take your pick, and good luck!

You've already quantified the values into a binomial event (present=1, absent=null or 0). If you don't want to invest in an analysis program you can add a series of columns in your spreadsheet that calculates the sum of the two columns of interest. Then sum the results of those columns. How many columns are you dealing with? If it's more than a dozen then you'll quickly run into a issue with the number of all possible combinations. If it's a large spreadsheet you'll need to write a program or use an analysis tool.