Countif multiple criteria within the same column
I need to count different model numbers within the same column. The model
numbers might be BLR, GHTR, HYRS,GUIT and so on. I need excel to count the different model numbers in this column but only if another column = "N" Any suggestions? |
Countif multiple criteria within the same column
=sumproduct(--(a1:a10="BLR"),--(b1:b10="N"))
change the range, but don't use the whole column. Harley wrote: I need to count different model numbers within the same column. The model numbers might be BLR, GHTR, HYRS,GUIT and so on. I need excel to count the different model numbers in this column but only if another column = "N" Any suggestions? -- Dave Peterson |
Countif multiple criteria within the same column
I dont' believe I explained this correctly. For example:
Column A Column B BHTY N BHRY N LNTY N GHRE U GHRT N LNUY U LNJH N BHHR N BHHT U LNTU N TYRU N BH GH LN TY 3 1 3 1 I need to break these down into 4 categories. There might be as many as 8 models for one category. Thanks. "Dave Peterson" wrote: =sumproduct(--(a1:a10="BLR"),--(b1:b10="N")) change the range, but don't use the whole column. Harley wrote: I need to count different model numbers within the same column. The model numbers might be BLR, GHTR, HYRS,GUIT and so on. I need excel to count the different model numbers in this column but only if another column = "N" Any suggestions? -- Dave Peterson |
Countif multiple criteria within the same column
You could set up a third column (Col C) and enter
=IF(B1="N",A1,"") copy down. then below this new column enter =SUMPRODUCT(1/COUNTIF(C1:C6,C1:C6)) adjust ranges as necessary. I tried to do this in one array formula, but couldn't quite get it. "Harley" wrote: I need to count different model numbers within the same column. The model numbers might be BLR, GHTR, HYRS,GUIT and so on. I need excel to count the different model numbers in this column but only if another column = "N" Any suggestions? |
Countif multiple criteria within the same column
sorry - left off the last half of that formula
=SUMPRODUCT(1/COUNTIF(C1:C6,C1:C6))-(--(COUNTIF(C1:C6,"")0)) "Harley" wrote: I need to count different model numbers within the same column. The model numbers might be BLR, GHTR, HYRS,GUIT and so on. I need excel to count the different model numbers in this column but only if another column = "N" Any suggestions? |
Countif multiple criteria within the same column
Assuming that A1:B11 contains your data, let D1:G1 contain BH, GH, LN,
and TY, and let C2 contain N, then enter the following formula in D2 and copy across... =SUMPRODUCT(--(LEFT($A$1:$A$11,2)=D$1),--($B$1:$B$11=$C2)) Hope this helps! In article , Harley wrote: I dont' believe I explained this correctly. For example: Column A Column B BHTY N BHRY N LNTY N GHRE U GHRT N LNUY U LNJH N BHHR N BHHT U LNTU N TYRU N BH GH LN TY 3 1 3 1 I need to break these down into 4 categories. There might be as many as 8 models for one category. Thanks. "Dave Peterson" wrote: =sumproduct(--(a1:a10="BLR"),--(b1:b10="N")) change the range, but don't use the whole column. Harley wrote: I need to count different model numbers within the same column. The model numbers might be BLR, GHTR, HYRS,GUIT and so on. I need excel to count the different model numbers in this column but only if another column = "N" Any suggestions? -- Dave Peterson |
All times are GMT +1. The time now is 08:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com