VLookUp - Sum - Different criterias

[stevekho]

Hello,

Here is my problem and I hope you will be able to help:

In have in my column A, 100 different States
In My column B, I have the Corresponding Counties
In Row 2, I have some dates (from 2008 Sep 1 until now)

Basically I want to sum all the values of August 17th for instance that are in State X [Column A] and in Counties Y [ Column B].

I want to be able to change the date and still get the values.

I tried SumIfS but couldnt get it :/

Thanks!!

 

[VoG]

Try like this

Date in E1

=SUMPRODUCT(--(A1:A100="TX"),--(B1:B100="County1"),--(C1:C100=$E$1),D1:D100)

 

[stevekho]

Nice

Do you think there is a way to put a range of date also? If I want just a monthly average for instance

Thanks!

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">STATE</td><td style="font-weight: bold;text-align: center;;">COUNTY</td><td style="font-weight: bold;text-align: center;;">8/16/2011</td><td style="font-weight: bold;text-align: center;background-color: #CCFFFF;;">8/17/2011</td><td style="font-weight: bold;text-align: center;;">8/18/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">FL</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #CCFFCC;;">WI</td><td style="text-align: center;background-color: #99CCFF;;">DANE</td><td style="text-align: center;;">4</td><td style="text-align: center;background-color: #FFFF99;;">5</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #CCFFCC;;">WI</td><td style="text-align: center;;">PORTAGE</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #CCFFCC;;">WI</td><td style="text-align: center;background-color: #99CCFF;;">DANE</td><td style="text-align: center;;">6</td><td style="text-align: center;background-color: #FFFF99;;">7</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">FL</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;;">STATE</td><td style="font-weight: bold;text-align: center;;">COUNTY</td><td style="font-weight: bold;text-align: center;;">DATE</td><td style="font-weight: bold;text-align: center;;">TOTAL</td><td style="font-weight: bold;text-align: center;;">AVG</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #CCFFCC;;">WI</td><td style="text-align: center;background-color: #99CCFF;;">DANE</td><td style="text-align: center;background-color: #CCFFFF;;">8/17/2011</td><td style="text-align: center;background-color: #FFFF99;;">12</td><td style="text-align: center;background-color: #FFFF99;;">6</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D11</th><td style="text-align:left">=DSUM(<font color="Blue">A2:E7,MATCH(<font color="Red">C11,2:2,0</font>),A10:B11</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E11</th><td style="text-align:left">=DAVERAGE(<font color="Blue">A2:E7,MATCH(<font color="Red">C11,2:2,0</font>),A10:B11</font>)</td></tr></tbody></table></td></tr></table><br />

 

[stevekho]

Excellent!

Any way to specify a range of date?? and sum

 

[T. Valko]

Hello,

Here is my problem and I hope you will be able to help:

In have in my column A, 100 different States
In My column B, I have the Corresponding Counties
In Row 2, I have some dates (from 2008 Sep 1 until now)

Basically I want to sum all the values of August 17th for instance that are in State X [Column A] and in Counties Y [ Column B].

I want to be able to change the date and still get the values.

I tried SumIfS but couldnt get it :/

Thanks!!

Is this what you had in mind...

Data table:

Book1
	A	B	C	D	E	F	G
1	State	County	1/1/2011	1/2/2011	1/3/2011	1/4/2011	1/5/2011
2	1	A	53	28	74	5	70
3	1	A	69	4	43	89	66
4	2	B	11	90	9	44	91
5	3	C	36	15	60	47	2
Sheet1

Criteria:

Book1
	A	B	C	D	E
10	State	County	From	To	Total
11	1	A	1/2/2011	1/4/2011	243
Sheet1

This formula entered in E11:

=SUMPRODUCT((A2:A5=A11)*(B2:B5=B11)*(C1:G1>=C11)*(C1:G1<=D11)*C2:G5)

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">STATE</td><td style="font-weight: bold;text-align: center;;">COUNTY</td><td style="font-weight: bold;text-align: center;;">8/16/2011</td><td style="font-weight: bold;text-align: center;background-color: #CCFFFF;;">8/17/2011</td><td style="font-weight: bold;text-align: center;background-color: #CCFFFF;;">8/18/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">FL</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #CCFFCC;;">WI</td><td style="text-align: center;background-color: #99CCFF;;">DANE</td><td style="text-align: center;;">4</td><td style="text-align: center;background-color: #FFFF99;;">5</td><td style="text-align: center;background-color: #FFFF99;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #CCFFCC;;">WI</td><td style="text-align: center;;">PORTAGE</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #CCFFCC;;">WI</td><td style="text-align: center;background-color: #99CCFF;;">DANE</td><td style="text-align: center;;">6</td><td style="text-align: center;background-color: #FFFF99;;">7</td><td style="text-align: center;background-color: #FFFF99;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">FL</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;;">STATE</td><td style="font-weight: bold;text-align: center;;">COUNTY</td><td style="font-weight: bold;text-align: center;;">DATE Start</td><td style="font-weight: bold;text-align: center;;">DATE End</td><td style="font-weight: bold;text-align: center;;">TOTAL</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #CCFFCC;;">WI</td><td style="text-align: center;background-color: #99CCFF;;">DANE</td><td style="text-align: center;background-color: #CCFFFF;;">8/17/2011</td><td style="text-align: center;background-color: #CCFFFF;;">8/18/2011</td><td style="text-align: center;background-color: #FFFF99;;">26</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E11</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">A3:A7=A11</font>)*(<font color="Red">B3:B7=B11</font>)*(<font color="Red">C2:E2>=C11</font>)*(<font color="Red">C2:E2<=D11</font>)*C3:E7</font>)</td></tr></tbody></table></td></tr></table><br />