Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dsum with date criteria | Excel Worksheet Functions | |||
DSUM and DCount when criteria values are similar | Excel Worksheet Functions | |||
Use of the DSUM formula to find exact matches in datatables | Excel Worksheet Functions | |||
DSUM frustrations | Excel Worksheet Functions | |||
DSUM frustrations | Excel Worksheet Functions |