Array req?

[kizzie37]

I am struggling with a count formulas giving incorrect results

column a has a number of date from this year (i.e 01/04/2011)
column b has a Y/N for an event

I am needing to count the number of those with Y against them, that fall under say month 1 (Jan)

Using countif and month etc, but if there is no instance of that month, the count is retunring a "1" is this because the "month" formula is not correctly finding the month??.

any advice?

 

[MrKowz]

Try:

<table width="85%" 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"></th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">MONTH(<font color="Green">A1:A100</font>)=1</font>),--(<font color="Red">B1:B100="Y"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[T. Valko]

I am struggling with a count formulas giving incorrect results

column a has a number of date from this year (i.e 01/04/2011)
column b has a Y/N for an event

I am needing to count the number of those with Y against them, that fall under say month 1 (Jan)

Using countif and month etc, but if there is no instance of that month, the count is retunring a "1" is this because the "month" formula is not correctly finding the month??.

any advice?

One way...

Assuming there are no empty cells within the date range.

=SUMPRODUCT(--(MONTH(A2:A10)=n),--(B2:B10="Y"))

Where n = the month number

For January:

=SUMPRODUCT(--(MONTH(A2:A10)=1),--(B2:B10="Y"))

 

[kizzie37]

Mr kowz, thanks works great, question, what are the double "--" for in the formula, not come accorss that before?

 

[MrKowz]

The -- coerces the conditional statement within the parentheses into a 1 or a 0. By doing this, it creates an array of 1s and 0s. It does this by taking each TRUE/FALSE statement and multiplying it by -1 twice.


Lets take the following data for example:
Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><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">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">A</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">B</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">C</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">D</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center; TEXT-DECORATION: underline">Date</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center; TEXT-DECORATION: underline">Amount</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center; TEXT-DECORATION: underline">Type</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center; TEXT-DECORATION: underline">Branch</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">12/1/2010</TD><TD style="TEXT-ALIGN: right">4000</TD><TD>Deposit</TD><TD>East</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">12/1/2010</TD><TD style="TEXT-ALIGN: right">6000</TD><TD>Deposit</TD><TD>East</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">12/1/2010</TD><TD style="TEXT-ALIGN: right">2000</TD><TD>Withdrawl</TD><TD>East</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">12/1/2010</TD><TD style="TEXT-ALIGN: right">3000</TD><TD>Deposit</TD><TD>West</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">12/2/2010</TD><TD style="TEXT-ALIGN: right">5000</TD><TD>Withdrawl</TD><TD>East</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">12/2/2010</TD><TD style="TEXT-ALIGN: right">5000</TD><TD>Withdrawl</TD><TD>West</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">12/3/2010</TD><TD style="TEXT-ALIGN: right">10000</TD><TD>Deposit</TD><TD>West</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">12/3/2010</TD><TD style="TEXT-ALIGN: right">4000</TD><TD>Withdrawl</TD><TD>East</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet3</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>


And we want to use this SUMPRODUCT function to find the number of Deposits at the East Branch:
=SUMPRODUCT(--(C2:C9=”Deposit”),--(D2:D9=”East”))


What the SUMPRODUCT first does (before it evaluates the double unary -- operator), is internally look at each item on a TRUE/FALSE basis:
Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><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">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">A</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">B</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">C</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">D</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center; TEXT-DECORATION: underline">Date</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center; TEXT-DECORATION: underline">Amount</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center; TEXT-DECORATION: underline">Type</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center; TEXT-DECORATION: underline">Branch</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">12/1/2010</TD><TD style="TEXT-ALIGN: right">4000</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">12/1/2010</TD><TD style="TEXT-ALIGN: right">6000</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">12/1/2010</TD><TD style="TEXT-ALIGN: right">2000</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">12/1/2010</TD><TD style="TEXT-ALIGN: right">3000</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">12/2/2010</TD><TD style="TEXT-ALIGN: right">5000</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">12/2/2010</TD><TD style="TEXT-ALIGN: right">5000</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">12/3/2010</TD><TD style="TEXT-ALIGN: right">10000</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">12/3/2010</TD><TD style="TEXT-ALIGN: right">4000</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet3</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>


The first condition: (C2:C9=”Deposit”) returns the array {TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}

The second condition: (D2:D9=”East”) returns the array
{TRUE,TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE}


Since TRUE/FALSE holds values of 1/0, we multiply them by -1 twice (using the double unary operator) to convert them to a positive, numerical, value:

--(TRUE) = (-1)(-1)(TRUE) = (-1)(-1) = 1
--(FALSE) = (-1)(-1)(FALSE) = (-1)(0) = 0

So now, SUMPRODUCT has two arrays to look at:

{1,1,0,1,0,0,1,0} and {1,1,0,0,0,0,0,0}

Following the nature of the SUMPRODUCT formula, it now multiplies these two arrays together and adds those products:

1*1 + 1*1 + 0*0 + 1*0 + 0*0 + 0*0 + 1*0 + 0*0 = 1+1+0+0+0+0+0+0 = 2

 

[T. Valko]

what are the double "--" for in the formula, not come accorss that before?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html