|
countif 2 criteria
Hi
Struggling to get this to work, despite looking at the treads. Using excel 2003. trying to count how many times 5710 in col G has "6" in Col H col-G col-H 5710 --- 7 5710 ----6 5694 ----6 5678----3 5710 ---6 =SUMPRODUCT(--(G2:G6=5710),--(H2:H6=6)) Would like to add 5694 as well as 5710, is this possible and does if have to be on the same sheet? thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201003/1 |
countif 2 criteria
Would like to add 5694 as well as 5710, is this possible
Yes, here's one way... Use cells to hold the criteria: J2 = 5710 K2 = 5694 L2 = 6 =SUMPRODUCT(--(ISNUMBER(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2)) does if have to be on the same sheet? Does what have to be on the same sheet? The formula? No With the criteria on the same sheet as the formula: =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!G2:G6,J2:K2,0))),--(Sheet1!H2:H6=L2)) -- Biff Microsoft Excel MVP "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a5b290940805a@uwe... Hi Struggling to get this to work, despite looking at the treads. Using excel 2003. trying to count how many times 5710 in col G has "6" in Col H col-G col-H 5710 --- 7 5710 ----6 5694 ----6 5678----3 5710 ---6 =SUMPRODUCT(--(G2:G6=5710),--(H2:H6=6)) Would like to add 5694 as well as 5710, is this possible and does if have to be on the same sheet? thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201003/1 |
countif 2 criteria
=SUMPRODUCT(--(G2:G6={5710,5694}),--(H2:H6=6))
Can't use the double unary in that syntax. =SUMPRODUCT((G2:G6={5710,5694})*(H2:H6=6)) -- Biff Microsoft Excel MVP "Don Guillett" wrote in message ... try =SUMPRODUCT(--(G2:G6={5710,5694}),--(H2:H6=6)) -- Don Guillett Microsoft MVP Excel SalesAid Software "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a5b290940805a@uwe... Hi Struggling to get this to work, despite looking at the treads. Using excel 2003. trying to count how many times 5710 in col G has "6" in Col H col-G col-H 5710 --- 7 5710 ----6 5694 ----6 5678----3 5710 ---6 =SUMPRODUCT(--(G2:G6=5710),--(H2:H6=6)) Would like to add 5694 as well as 5710, is this possible and does if have to be on the same sheet? thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201003/1 |
countif 2 criteria
wow
thank you both for the speedy response Could not get Don's to work- got #Value! error. wonder what I am doing wrong T.Valko worked perfect, using the second formula for identifying on different sheet regards T. Valko wrote: Would like to add 5694 as well as 5710, is this possible Yes, here's one way... Use cells to hold the criteria: J2 = 5710 K2 = 5694 L2 = 6 =SUMPRODUCT(--(ISNUMBER(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2)) does if have to be on the same sheet? Does what have to be on the same sheet? The formula? No With the criteria on the same sheet as the formula: =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!G2:G6,J2:K2,0))),--(Sheet1!H2:H6=L2)) Hi [quoted text clipped - 16 lines] thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201003/1 |
countif 2 criteria
one last Q before you retire to bed ;-)
how to countif value of 6 (L2), for those not in range J2:K2 ? regards T. Valko wrote: Would like to add 5694 as well as 5710, is this possible Yes, here's one way... Use cells to hold the criteria: J2 = 5710 K2 = 5694 L2 = 6 =SUMPRODUCT(--(ISNUMBER(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2)) does if have to be on the same sheet? Does what have to be on the same sheet? The formula? No With the criteria on the same sheet as the formula: =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!G2:G6,J2:K2,0))),--(Sheet1!H2:H6=L2)) Hi [quoted text clipped - 16 lines] thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201003/1 |
countif 2 criteria
If I undersatand what you want...
=SUMPRODUCT(--(ISNA(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2)) -- Biff Microsoft Excel MVP "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a5b3395ed298e@uwe... one last Q before you retire to bed ;-) how to countif value of 6 (L2), for those not in range J2:K2 ? regards T. Valko wrote: Would like to add 5694 as well as 5710, is this possible Yes, here's one way... Use cells to hold the criteria: J2 = 5710 K2 = 5694 L2 = 6 =SUMPRODUCT(--(ISNUMBER(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2)) does if have to be on the same sheet? Does what have to be on the same sheet? The formula? No With the criteria on the same sheet as the formula: =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!G2:G6,J2:K2,0))),--(Sheet1!H2:H6=L2)) Hi [quoted text clipped - 16 lines] thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201003/1 |
countif 2 criteria
yep- thank you again
T. Valko wrote: If I undersatand what you want... =SUMPRODUCT(--(ISNA(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2)) one last Q before you retire to bed ;-) how to countif value of 6 (L2), for those not in range J2:K2 ? [quoted text clipped - 27 lines] thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201003/1 |
countif 2 criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a5baf921c4105@uwe... yep- thank you again T. Valko wrote: If I undersatand what you want... =SUMPRODUCT(--(ISNA(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2)) one last Q before you retire to bed ;-) how to countif value of 6 (L2), for those not in range J2:K2 ? [quoted text clipped - 27 lines] thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201003/1 |
countif 2 criteria
Damn. The one time I didn't TEST
-- Don Guillett Microsoft MVP Excel SalesAid Software "T. Valko" wrote in message ... =SUMPRODUCT(--(G2:G6={5710,5694}),--(H2:H6=6)) Can't use the double unary in that syntax. =SUMPRODUCT((G2:G6={5710,5694})*(H2:H6=6)) -- Biff Microsoft Excel MVP "Don Guillett" wrote in message ... try =SUMPRODUCT(--(G2:G6={5710,5694}),--(H2:H6=6)) -- Don Guillett Microsoft MVP Excel SalesAid Software "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a5b290940805a@uwe... Hi Struggling to get this to work, despite looking at the treads. Using excel 2003. trying to count how many times 5710 in col G has "6" in Col H col-G col-H 5710 --- 7 5710 ----6 5694 ----6 5678----3 5710 ---6 =SUMPRODUCT(--(G2:G6=5710),--(H2:H6=6)) Would like to add 5694 as well as 5710, is this possible and does if have to be on the same sheet? thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201003/1 |
| All times are GMT +1. The time now is 10:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com