DCOUNT? DSUM?

P

Paul W Smith

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
 
P

Paul W Smith

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.
 
P

Peo Sjoblom

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
 
P

Peo Sjoblom

Just add another range

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