Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF MULTIPLE CRITERIA | Excel Discussion (Misc queries) | |||
Multiple Functions In One Column | Excel Worksheet Functions | |||
Multiple Criteria using countif | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |