ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   DCOUNT? DSUM? (https://www.excelbanter.com/excel-worksheet-functions/64513-dcount-dsum.html)

Paul W Smith
DCOUNT? DSUM?
 
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
DCOUNT? DSUM?
 
=SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))


--

Regards,

Peo Sjoblom


"Paul W Smith" wrote in message
...
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
DCOUNT? DSUM?
 
what does the -- mean?



"Peo Sjoblom" wrote in message
...
=SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))


--

Regards,

Peo Sjoblom


"Paul W Smith" wrote in message
...
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
DCOUNT? DSUM?
 
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" wrote in message
...
=SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))


--

Regards,

Peo Sjoblom


"Paul W Smith" wrote in message
...
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
DCOUNT? DSUM?
 

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 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" wrote in message
...
what does the -- mean?



"Peo Sjoblom" wrote in message
...
=SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))


--

Regards,

Peo Sjoblom


"Paul W Smith" wrote in message
...
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
DCOUNT? DSUM?
 
Just add another range

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

--

Regards,

Peo Sjoblom

"Paul W Smith" wrote in message
...
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" wrote in message
...
=SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))


--

Regards,

Peo Sjoblom


"Paul W Smith" wrote in message
...
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
DCOUNT? DSUM?
 
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" wrote in message
...
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" wrote in message
...
=SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))


--

Regards,

Peo Sjoblom


"Paul W Smith" wrote in message
...
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
DCOUNT? DSUM?
 
Great and very informative article - thanks.


"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
=SUMPRODUCT(--(A2:A10=1),--(B2:B10=TRUE))


--

Regards,

Peo Sjoblom


"Paul W Smith" wrote in message
...
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


All times are GMT +1. The time now is 06:03 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com