Averageifs with and statement

[youremyboyblue]

I am trying to get the average subsidy (column H) amount for children of a certain age (column e) if the license type in column p is either "Family Home" or "Family Group". I have about 200,000 rows of data. I need to get the average subsidy amount for children not yet 1, 1 year olds, 2 year olds, 3 year olds, etc. and separate out the average subsidy amount by age and by provider license type ("Family Group, Family Home, Licensed, etc). In the end I am trying to create a matrix with provider license type and ages of children.


Excel 2010<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>MONTH</TD><TD>CASE NUMBER</TD><TD>PID</TD><TD>CLIENT</TD><TD>AGE</TD><TD>SCHOOL</TD><TD>COUNTY</TD><TD>SUBSIDY</TD><TD>INC ADJ</TD><TD>MIN.</TD><TD>HOURLY RATE</TD><TD>MLMR</TD><TD>PROVIDER CHARGE</TD><TD>EXTRA PAYMENT</TD><TD>PROVIDER NAME</TD><TD>Provider Type</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">201009</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">536</TD><TD>Bob</TD><TD style="TEXT-ALIGN: right">1</TD><TD>N</TD><TD>Cache</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">396</TD><TD style="TEXT-ALIGN: right">412</TD><TD style="TEXT-ALIGN: right">396</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right">0</TD><TD>Kara</TD><TD>Family Home</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">201101</TD><TD style="TEXT-ALIGN: right">124</TD><TD style="TEXT-ALIGN: right">548</TD><TD>Joe</TD><TD style="TEXT-ALIGN: right">1</TD><TD>N</TD><TD>Washington</TD><TD style="TEXT-ALIGN: right">480</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">277</TD><TD style="TEXT-ALIGN: right">287</TD><TD style="TEXT-ALIGN: right">277</TD><TD style="TEXT-ALIGN: right">661</TD><TD style="TEXT-ALIGN: right">0</TD><TD>Kimberly</TD><TD>Family Group</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">201009</TD><TD style="TEXT-ALIGN: right">125</TD><TD style="TEXT-ALIGN: right">785</TD><TD>Mike</TD><TD style="TEXT-ALIGN: right">4</TD><TD>N</TD><TD>Salt Lake</TD><TD style="TEXT-ALIGN: right">230</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">300</TD><TD style="TEXT-ALIGN: right">580</TD><TD style="TEXT-ALIGN: right">450</TD><TD style="TEXT-ALIGN: right">300</TD><TD style="TEXT-ALIGN: right">0</TD><TD>Katrina</TD><TD>Licensed</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">201007</TD><TD style="TEXT-ALIGN: right">126</TD><TD style="TEXT-ALIGN: right">196</TD><TD>Shelly</TD><TD style="TEXT-ALIGN: right">2</TD><TD>N</TD><TD>Davis</TD><TD style="TEXT-ALIGN: right">180</TD><TD style="TEXT-ALIGN: right">37</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">750</TD><TD style="TEXT-ALIGN: right">430</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">0</TD><TD>Vanessa</TD><TD>Residential Certificate</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">201008</TD><TD style="TEXT-ALIGN: right">127</TD><TD style="TEXT-ALIGN: right">482</TD><TD>Cindy</TD><TD style="TEXT-ALIGN: right">3</TD><TD>N</TD><TD>Salt Lake</TD><TD style="TEXT-ALIGN: right">650</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">364</TD><TD style="TEXT-ALIGN: right">364</TD><TD style="TEXT-ALIGN: right">396</TD><TD style="TEXT-ALIGN: right">456</TD><TD style="TEXT-ALIGN: right">0</TD><TD>Adla</TD><TD>Unlicensed</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet6​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #bbb 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>J2</TH><TD style="TEXT-ALIGN: left">=MIN(K2:M2)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>J3</TH><TD style="TEXT-ALIGN: left">=MIN(K3:M3)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>J4</TH><TD style="TEXT-ALIGN: left">=MIN(K4:M4)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>J5</TH><TD style="TEXT-ALIGN: left">=MIN(K5:M5)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>J6</TH><TD style="TEXT-ALIGN: left">=MIN(K6:M6)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[doublej_jj]

Sumproduct is a nifty trick:

IF Provider license types are in column AA starting in row 2 and
your ages are headers in row 1, try this in cell AB2

= Sumproduct(($E$2:$E$200000=AB$1)*($P$2:$P$200000=$AA2)*($H$2:$H$200000))

What happens here
- The E column from 2 to 200000 is checked to see if it matches the age header. If it does match, or is equal, the excel assume true which is numerically 1. If it is false, it is 0.
- Then it does the same for your provider license type.
- So anywhere either of those are false, you have a zero in your multiplication.
- The rest of the places have 1 and multiplies that by your subsidy.

Now Do Name Ranges
Age = $E$2:$E$200000
Licence = $P$2:$P$200000
Subsidy = $H$2:$H$200000

Formula is then
=sumproduct((Age=AB$1)*(License=$AA2)*(Subsidy))

You can copy this formula all over your matrix and it will work for every combination of license and age you have listed.

Good Luck

Jeff

 

[youremyboyblue]

How do I get the averages?

Thank you for your reply. How do I get from the sums in each cell in the matrix to the average for each cell in the matrix? And how would I combine two of the provider license types into one cell in the matrix?

For example, I want both "Family Group" and "Family Home" to be in the same cell (find the combined average subsidy for each age range with those two license types).

In the end the matrix should look like this:

Excel 2010<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Provider Type</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">0 to <24 months</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">2 Years Old</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">3 Years Old</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">4 Years Old</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">5 Years Old</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">6+ Years Old</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">342</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">307</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">307</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">292</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">290</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">239</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Unlicensed</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">License Exempt</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">506</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">465</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">438</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">414</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">407</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">304</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Residential Certificate</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">License Exempt Center</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">470</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">431</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">427</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">426</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">398</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">337</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">24</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Family Home</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">25</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Family Group</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">26</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">564</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">500</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">476</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">460</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">432</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">396</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">27</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">28</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Licensed or Accredited Center</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #666666; BORDER-RIGHT: black 1px solid"></TD></TR></TBODY></TABLE>

Weighted Mean 980 data​

 

[doublej_jj]

Your final matrix
* Somewhere in there, your going to have to put 2, 3, 4, 5, 6 in your header, you can hide them if you want.

* Modify the formula to be Age<=AB$1 for under 2 and Age>=AF$1 for over 6

The average:
* Divide each cell by the count
=Sumproduct((Age=AB$1)*(License=$AA2)*(Subsidy)) / sumproduct((Age=AB$1)*(License=$AA2)*1)


The Grouping:
* Add a column to your data that creates your groups - Family Group & Family Home would all be labeled Group 1, License & Unlicensed would be Group 2, etc.

Then for those areas, change the formula to take out the Licence = and change it to a Group =...., where group will be $Q$2:$Q$200000

There might me a little more elegant way to resolve this, but I don't have more time. I know that I have only given you a high level idea, but dig in and learn.

Jeff