software

I want a formula that will tell me how many 'Trues' I have, when Team = A
and league = 1 etc.

Team League ResultA
A 1 TRUE
B 1 TRUE
C 1 TRUE
A 2 TRUE
B 2 TRUE
C 2 FALSE
A 3 TRUE
B 3 TRUE
C 3 TRUE
A 1 TRUE
B 1 TRUE
C 1 FALSE
A 2 FALSE
B 2 FALSE
C 2 TRUE
A 3 FALSE
B 3 FALSE
C 3 FALSE
=SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))--

Regards,

Peo Sjoblomquot;Paul W Smithquot; gt; wrote in message
...
gt; I want a formula that will tell me how many 'Trues' I have, when Team = A
gt; and league = 1 etc.
gt;
gt; Team League ResultA
gt; A 1 TRUE
gt; B 1 TRUE
gt; C 1 TRUE
gt; A 2 TRUE
gt; B 2 TRUE
gt; C 2 FALSE
gt; A 3 TRUE
gt; B 3 TRUE
gt; C 3 TRUE
gt; A 1 TRUE
gt; B 1 TRUE
gt; C 1 FALSE
gt; A 2 FALSE
gt; B 2 FALSE
gt; C 2 TRUE
gt; A 3 FALSE
gt; B 3 FALSE
gt; C 3 FALSE
gt;
gt;
what does the -- mean?
quot;Peo Sjoblomquot; gt; wrote in message
...
gt; =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt;
gt; quot;Paul W Smithquot; gt; wrote in message
gt; ...
gt;gt; I want a formula that will tell me how many 'Trues' I have, when Team = A
gt;gt; and league = 1 etc.
gt;gt;
gt;gt; Team League ResultA
gt;gt; A 1 TRUE
gt;gt; B 1 TRUE
gt;gt; C 1 TRUE
gt;gt; A 2 TRUE
gt;gt; B 2 TRUE
gt;gt; C 2 FALSE
gt;gt; A 3 TRUE
gt;gt; B 3 TRUE
gt;gt; C 3 TRUE
gt;gt; A 1 TRUE
gt;gt; B 1 TRUE
gt;gt; C 1 FALSE
gt;gt; A 2 FALSE
gt;gt; B 2 FALSE
gt;gt; C 2 TRUE
gt;gt; A 3 FALSE
gt;gt; B 3 FALSE
gt;gt; C 3 FALSE
gt;gt;
gt;gt;
gt;
gt;
I am now guessing that -- signifies an array function.

However you have misunderstood my issue. Have three columns of data

Team League Result
A 1 True
B 1 True

Etc.quot;Peo Sjoblomquot; gt; wrote in message
...
gt; =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt;
gt; quot;Paul W Smithquot; gt; wrote in message
gt; ...
gt;gt; I want a formula that will tell me how many 'Trues' I have, when Team = A
gt;gt; and league = 1 etc.
gt;gt;
gt;gt; Team League ResultA
gt;gt; A 1 TRUE
gt;gt; B 1 TRUE
gt;gt; C 1 TRUE
gt;gt; A 2 TRUE
gt;gt; B 2 TRUE
gt;gt; C 2 FALSE
gt;gt; A 3 TRUE
gt;gt; B 3 TRUE
gt;gt; C 3 TRUE
gt;gt; A 1 TRUE
gt;gt; B 1 TRUE
gt;gt; C 1 FALSE
gt;gt; A 2 FALSE
gt;gt; B 2 FALSE
gt;gt; C 2 TRUE
gt;gt; A 3 FALSE
gt;gt; B 3 FALSE
gt;gt; C 3 FALSE
gt;gt;
gt;gt;
gt;
gt;

assume you have 2 TRUE in B where A is 1 then it would look like

=SUMPRODUCT(--({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALS E}),--({TRUE
;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}))

Then the unary minuses would turn it into

=SUMPRODUCT({1;0;1;0;1;0;1;0;0},{1;1;0;1;1;0;0;0;0 })

and when the arrays are multiplied it will return 2the unary minuses will convert TRUE FALSE to 1 or 0 thus you can use the
built in format of SUMPRODUCT, you can use

=SUMPRODUCT((A2:A10=1)*(B2:B10=TRUE))

as well but if you also want to sum a range the latter formula

=SUMPRODUCT((A2:A10=1)*(B2:B10=TRUE)*(C2:C10))

will sum C where A is 1 and B is TRUE however if C has a text value like a
blank from another formula =quot;quot; it will return a #VALUE! error while

=SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE),C2:C10)

will ignore the text
--

Regards,

Peo Sjoblom
quot;Paul W Smithquot; gt; wrote in message
...
gt; what does the -- mean?
gt;
gt;
gt;
gt; quot;Peo Sjoblomquot; gt; wrote in message
gt; ...
gt; gt; =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt;
gt; gt; quot;Paul W Smithquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; I want a formula that will tell me how many 'Trues' I have, when Team =
A
gt; gt;gt; and league = 1 etc.
gt; gt;gt;
gt; gt;gt; Team League ResultA
gt; gt;gt; A 1 TRUE
gt; gt;gt; B 1 TRUE
gt; gt;gt; C 1 TRUE
gt; gt;gt; A 2 TRUE
gt; gt;gt; B 2 TRUE
gt; gt;gt; C 2 FALSE
gt; gt;gt; A 3 TRUE
gt; gt;gt; B 3 TRUE
gt; gt;gt; C 3 TRUE
gt; gt;gt; A 1 TRUE
gt; gt;gt; B 1 TRUE
gt; gt;gt; C 1 FALSE
gt; gt;gt; A 2 FALSE
gt; gt;gt; B 2 FALSE
gt; gt;gt; C 2 TRUE
gt; gt;gt; A 3 FALSE
gt; gt;gt; B 3 FALSE
gt; gt;gt; C 3 FALSE
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
Just add another range

=SUMPRODUCT(--(TeamRange=quot;Aquot;),--(LeagueRange=1),--(ResultRange=TRUE))

--

Regards,

Peo Sjoblom

quot;Paul W Smithquot; gt; wrote in message
...
gt; I am now guessing that -- signifies an array function.
gt;
gt; However you have misunderstood my issue. Have three columns of data
gt;
gt; Team League Result
gt; A 1 True
gt; B 1 True
gt;
gt; Etc.
gt;
gt;
gt; quot;Peo Sjoblomquot; gt; wrote in message
gt; ...
gt; gt; =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt;
gt; gt; quot;Paul W Smithquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; I want a formula that will tell me how many 'Trues' I have, when Team =
A
gt; gt;gt; and league = 1 etc.
gt; gt;gt;
gt; gt;gt; Team League ResultA
gt; gt;gt; A 1 TRUE
gt; gt;gt; B 1 TRUE
gt; gt;gt; C 1 TRUE
gt; gt;gt; A 2 TRUE
gt; gt;gt; B 2 TRUE
gt; gt;gt; C 2 FALSE
gt; gt;gt; A 3 TRUE
gt; gt;gt; B 3 TRUE
gt; gt;gt; C 3 TRUE
gt; gt;gt; A 1 TRUE
gt; gt;gt; B 1 TRUE
gt; gt;gt; C 1 FALSE
gt; gt;gt; A 2 FALSE
gt; gt;gt; B 2 FALSE
gt; gt;gt; C 2 TRUE
gt; gt;gt; A 3 FALSE
gt; gt;gt; B 3 FALSE
gt; gt;gt; C 3 FALSE
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
No, it doesn't at all . See
www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;Paul W Smithquot; gt; wrote in message
...
gt; I am now guessing that -- signifies an array function.
gt;
gt; However you have misunderstood my issue. Have three columns of data
gt;
gt; Team League Result
gt; A 1 True
gt; B 1 True
gt;
gt; Etc.
gt;
gt;
gt; quot;Peo Sjoblomquot; gt; wrote in message
gt; ...
gt; gt; =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt;
gt; gt; quot;Paul W Smithquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; I want a formula that will tell me how many 'Trues' I have, when Team =
A
gt; gt;gt; and league = 1 etc.
gt; gt;gt;
gt; gt;gt; Team League ResultA
gt; gt;gt; A 1 TRUE
gt; gt;gt; B 1 TRUE
gt; gt;gt; C 1 TRUE
gt; gt;gt; A 2 TRUE
gt; gt;gt; B 2 TRUE
gt; gt;gt; C 2 FALSE
gt; gt;gt; A 3 TRUE
gt; gt;gt; B 3 TRUE
gt; gt;gt; C 3 TRUE
gt; gt;gt; A 1 TRUE
gt; gt;gt; B 1 TRUE
gt; gt;gt; C 1 FALSE
gt; gt;gt; A 2 FALSE
gt; gt;gt; B 2 FALSE
gt; gt;gt; C 2 TRUE
gt; gt;gt; A 3 FALSE
gt; gt;gt; B 3 FALSE
gt; gt;gt; C 3 FALSE
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
Great and very informative article - thanks.quot;Bob Phillipsquot; gt; wrote in message
...
gt; No, it doesn't at all . See
gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt; (remove nothere from the email address if mailing direct)
gt;
gt;
gt; quot;Paul W Smithquot; gt; wrote in message
gt; ...
gt;gt; I am now guessing that -- signifies an array function.
gt;gt;
gt;gt; However you have misunderstood my issue. Have three columns of data
gt;gt;
gt;gt; Team League Result
gt;gt; A 1 True
gt;gt; B 1 True
gt;gt;
gt;gt; Etc.
gt;gt;
gt;gt;
gt;gt; quot;Peo Sjoblomquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt;
gt;gt; gt; Regards,
gt;gt; gt;
gt;gt; gt; Peo Sjoblom
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Paul W Smithquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt;gt; I want a formula that will tell me how many 'Trues' I have, when Team
gt;gt; gt;gt; =
gt; A
gt;gt; gt;gt; and league = 1 etc.
gt;gt; gt;gt;
gt;gt; gt;gt; Team League ResultA
gt;gt; gt;gt; A 1 TRUE
gt;gt; gt;gt; B 1 TRUE
gt;gt; gt;gt; C 1 TRUE
gt;gt; gt;gt; A 2 TRUE
gt;gt; gt;gt; B 2 TRUE
gt;gt; gt;gt; C 2 FALSE
gt;gt; gt;gt; A 3 TRUE
gt;gt; gt;gt; B 3 TRUE
gt;gt; gt;gt; C 3 TRUE
gt;gt; gt;gt; A 1 TRUE
gt;gt; gt;gt; B 1 TRUE
gt;gt; gt;gt; C 1 FALSE
gt;gt; gt;gt; A 2 FALSE
gt;gt; gt;gt; B 2 FALSE
gt;gt; gt;gt; C 2 TRUE
gt;gt; gt;gt; A 3 FALSE
gt;gt; gt;gt; B 3 FALSE
gt;gt; gt;gt; C 3 FALSE
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;