DCOUNT? DSUM?

Paul W Smith · 01-11-2006, 10:00 AM

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

Peo Sjoblom · 01-11-2006, 10:10 AM

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

--

Regards,

Peo Sjoblom

"Paul W Smith" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Paul W Smith · 01-11-2006, 10:25 AM

what does the -- mean?

"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "Paul W Smith" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

Peo Sjoblom · 01-11-2006, 10:35 AM

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;FALSE}),--({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 2

the 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 ="" it will return a #VALUE! error while

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

will ignore the text

--

Regards,

Peo Sjoblom

"Paul W Smith" <[email protected]> wrote in message
news:[email protected]...
> what does the -- mean?
>
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "Paul W Smith" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

Paul W Smith · 01-11-2006, 10:25 AM

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.

"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "Paul W Smith" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

Peo Sjoblom · 01-11-2006, 10:40 AM

Just add another range

=SUMPRODUCT(--(TeamRange="A"),--(LeagueRange=1),--(ResultRange=TRUE))

--

Regards,

Peo Sjoblom

"Paul W Smith" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "Paul W Smith" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

Bob Phillips · 01-11-2006, 10:45 AM

No, it doesn't at all . See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Paul W Smith" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "Paul W Smith" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

Paul W Smith · 01-11-2006, 11:10 AM

Great and very informative article - thanks.

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> No, it doesn't at all . See
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Paul W Smith" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>>
>> "Peo Sjoblom" <[email protected]> wrote in message
>> news:[email protected]...
>> > =SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))
>> >
>> >
>> > --
>> >
>> > Regards,
>> >
>> > Peo Sjoblom
>> >
>> >
>> > "Paul W Smith" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>