Excel- Add numbers in a column given that they're positive and given that they're associated with a value in a row

Question

Might be complicated, so let me give you some background:

I have a spreadsheet that contains the following data:

1. Column H contains dates from 1794- 2011 (and within ‘H’ there are multiple copies of each date for each legislator present in that date)
2. Column Q contains scores that range from -1 to 1 I want to calculate the average “positive” value in Column Q for each date in Column H, and then have it print out.

For example:

1794: Average Positive Value : .65, Average Negative:  -.20
1795: Average Positive Value: .75, Average Negative: -.11

I've tried searching around to figure out how to do this, but I don't know the search terms relevant. Would appreciate some assistance.

Thanks!

Answer 1

Here us a simple way using ARRAY Formulas. Please see SNAPSHOT

=IFERROR(AVERAGE(IF(($H$1:$H$7=K10)*($Q$1:$Q$7>0),$Q$1:$Q$7)),"")

You have to enter this using CTL + SHIFT + ENTER