| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIF
Hello -
I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
=SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1))
"Kell2604" wrote: Hello - I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
Thanks for your help Mike but I missed a part of my problem. The numerical
column doesn't just have the number 1 in it (I was trying to make my explanation simple) it has 1.1 & 1.2 & 1.7 etc. I need to count them all as if they were 1. Basically I just want to count the number of cells that have any value or number in them. So I'm having trouble using the SUMPRODUCT because they are not all equal to 1. Thanks again for your help - sorry for the confusion! "Mike" wrote: =SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1)) "Kell2604" wrote: Hello - I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
Use the INT function in your formula that Mike gave you, like so:
=SUMPRODUCT((A1:A5="Bus Jet OEM")*(INT(B1:B5)=1)) -- ** John C ** "Kell2604" wrote: Thanks for your help Mike but I missed a part of my problem. The numerical column doesn't just have the number 1 in it (I was trying to make my explanation simple) it has 1.1 & 1.2 & 1.7 etc. I need to count them all as if they were 1. Basically I just want to count the number of cells that have any value or number in them. So I'm having trouble using the SUMPRODUCT because they are not all equal to 1. Thanks again for your help - sorry for the confusion! "Mike" wrote: =SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1)) "Kell2604" wrote: Hello - I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
Maybe...
=SUMPRODUCT(--(A1:A5="Bus Jet OEM"),--(isnumber(B1:B5))) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kell2604 wrote: Thanks for your help Mike but I missed a part of my problem. The numerical column doesn't just have the number 1 in it (I was trying to make my explanation simple) it has 1.1 & 1.2 & 1.7 etc. I need to count them all as if they were 1. Basically I just want to count the number of cells that have any value or number in them. So I'm having trouble using the SUMPRODUCT because they are not all equal to 1. Thanks again for your help - sorry for the confusion! "Mike" wrote: =SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1)) "Kell2604" wrote: Hello - I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
Dave - Does isnumber just mean that it will count any cell that has a number
in it in the range b1 to b5?? Just want to make sure I understand what it's doing. Thanks!! Kelley "Dave Peterson" wrote: Maybe... =SUMPRODUCT(--(A1:A5="Bus Jet OEM"),--(isnumber(B1:B5))) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kell2604 wrote: Thanks for your help Mike but I missed a part of my problem. The numerical column doesn't just have the number 1 in it (I was trying to make my explanation simple) it has 1.1 & 1.2 & 1.7 etc. I need to count them all as if they were 1. Basically I just want to count the number of cells that have any value or number in them. So I'm having trouble using the SUMPRODUCT because they are not all equal to 1. Thanks again for your help - sorry for the confusion! "Mike" wrote: =SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1)) "Kell2604" wrote: Hello - I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
Hi Again,
I think I got it working by using B1:B50. Thanks Again - you guys are the best! "Mike" wrote: =SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1)) "Kell2604" wrote: Hello - I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
Yep.
(I guessed that's what you wanted. I may have guessed wrong.) Kell2604 wrote: Dave - Does isnumber just mean that it will count any cell that has a number in it in the range b1 to b5?? Just want to make sure I understand what it's doing. Thanks!! Kelley "Dave Peterson" wrote: Maybe... =SUMPRODUCT(--(A1:A5="Bus Jet OEM"),--(isnumber(B1:B5))) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kell2604 wrote: Thanks for your help Mike but I missed a part of my problem. The numerical column doesn't just have the number 1 in it (I was trying to make my explanation simple) it has 1.1 & 1.2 & 1.7 etc. I need to count them all as if they were 1. Basically I just want to count the number of cells that have any value or number in them. So I'm having trouble using the SUMPRODUCT because they are not all equal to 1. Thanks again for your help - sorry for the confusion! "Mike" wrote: =SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1)) "Kell2604" wrote: Hello - I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
Yes, that's exactly what I wanted!!
Thank you VERY much!! "Dave Peterson" wrote: Yep. (I guessed that's what you wanted. I may have guessed wrong.) Kell2604 wrote: Dave - Does isnumber just mean that it will count any cell that has a number in it in the range b1 to b5?? Just want to make sure I understand what it's doing. Thanks!! Kelley "Dave Peterson" wrote: Maybe... =SUMPRODUCT(--(A1:A5="Bus Jet OEM"),--(isnumber(B1:B5))) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kell2604 wrote: Thanks for your help Mike but I missed a part of my problem. The numerical column doesn't just have the number 1 in it (I was trying to make my explanation simple) it has 1.1 & 1.2 & 1.7 etc. I need to count them all as if they were 1. Basically I just want to count the number of cells that have any value or number in them. So I'm having trouble using the SUMPRODUCT because they are not all equal to 1. Thanks again for your help - sorry for the confusion! "Mike" wrote: =SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1)) "Kell2604" wrote: Hello - I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
=SUMPRODUCT((A1:A5="Bus Jet OEM")*(INT(B1:B5)=1))
This will count 1.1 thru 1.9 "Kell2604" wrote: Hi Again, I think I got it working by using B1:B50. Thanks Again - you guys are the best! "Mike" wrote: =SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1)) "Kell2604" wrote: Hello - I think I need the COUNTIF function for this but am not getting it to work. Help! I need to count a range of cells, if the cell next to it contains a certain value. Comm Spares Military OEM Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM 1 Bus Jet OEM Comm Spares Comm Spares 1 So, I want the cell containing my formula to count the 1's if the cell to it's left = Bus Jet OEM. I will have another cell counting the 1's if the cell to it's left = Comm Spares and so on. Hope my explanation makes sense. Thanks in advance for your help!! Kelley |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
| edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| Countif | Excel Discussion (Misc queries) | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Linear Mode
