| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
COUNTIF Function
I would like to look at one column (range1) in excel and if the word
"internal" appears, I would like to then look to the next column (range2) and count the total number of times the value is between 6 and 10... Internal 8 Internal 2 External 7 So in this data set, I would like my result to be equal to 1. Both internal and between 6 and 10. I can do them separately but cannot figure out how to combine the 2 formulas... =COUNTIF(range1, "internal") =COUNTIF(range2,"<=10")-COUNTIF(range2,"<6") Any help is greatly appreciated. |
|
#2
|
|||
|
|||
|
One way:
=SUMPRODUCT(--(range1="internal"),--(range2=6),-- (range2<=10)) HTH Jason Atlanta, GA -----Original Message----- I would like to look at one column (range1) in excel and if the word "internal" appears, I would like to then look to the next column (range2) and count the total number of times the value is between 6 and 10... Internal 8 Internal 2 External 7 So in this data set, I would like my result to be equal to 1. Both internal and between 6 and 10. I can do them separately but cannot figure out how to combine the 2 formulas... =COUNTIF(range1, "internal") =COUNTIF(range2,"<=10")-COUNTIF(range2,"<6") Any help is greatly appreciated. . |
|
#3
|
|||
|
|||
|
Thank you!
"Jason Morin" wrote: One way: =SUMPRODUCT(--(range1="internal"),--(range2=6),-- (range2<=10)) HTH Jason Atlanta, GA -----Original Message----- I would like to look at one column (range1) in excel and if the word "internal" appears, I would like to then look to the next column (range2) and count the total number of times the value is between 6 and 10... Internal 8 Internal 2 External 7 So in this data set, I would like my result to be equal to 1. Both internal and between 6 and 10. I can do them separately but cannot figure out how to combine the 2 formulas... =COUNTIF(range1, "internal") =COUNTIF(range2,"<=10")-COUNTIF(range2,"<6") Any help is greatly appreciated. . |
|
#4
|
|||
|
|||
|
What does the "--" in front of --(range1="internal") do?
"Meredith" wrote: Thank you! "Jason Morin" wrote: One way: =SUMPRODUCT(--(range1="internal"),--(range2=6),-- (range2<=10)) HTH Jason Atlanta, GA -----Original Message----- I would like to look at one column (range1) in excel and if the word "internal" appears, I would like to then look to the next column (range2) and count the total number of times the value is between 6 and 10... Internal 8 Internal 2 External 7 So in this data set, I would like my result to be equal to 1. Both internal and between 6 and 10. I can do them separately but cannot figure out how to combine the 2 formulas... =COUNTIF(range1, "internal") =COUNTIF(range2,"<=10")-COUNTIF(range2,"<6") Any help is greatly appreciated. . |
|
#5
|
|||
|
|||
|
What does the -- in front of the (range1 = "internal") do?
"Meredith" wrote: Thank you! "Jason Morin" wrote: One way: =SUMPRODUCT(--(range1="internal"),--(range2=6),-- (range2<=10)) HTH Jason Atlanta, GA -----Original Message----- I would like to look at one column (range1) in excel and if the word "internal" appears, I would like to then look to the next column (range2) and count the total number of times the value is between 6 and 10... Internal 8 Internal 2 External 7 So in this data set, I would like my result to be equal to 1. Both internal and between 6 and 10. I can do them separately but cannot figure out how to combine the 2 formulas... =COUNTIF(range1, "internal") =COUNTIF(range2,"<=10")-COUNTIF(range2,"<6") Any help is greatly appreciated. . |
|
#6
|
|||
|
|||
|
J.E. McGimpsey explains it at his site:
http://mcgimpsey.com/excel/formulae/doubleneg.html Marc Shivers wrote: What does the -- in front of the (range1 = "internal") do? "Meredith" wrote: Thank you! "Jason Morin" wrote: One way: =SUMPRODUCT(--(range1="internal"),--(range2=6),-- (range2<=10)) HTH Jason Atlanta, GA -----Original Message----- I would like to look at one column (range1) in excel and if the word "internal" appears, I would like to then look to the next column (range2) and count the total number of times the value is between 6 and 10... Internal 8 Internal 2 External 7 So in this data set, I would like my result to be equal to 1. Both internal and between 6 and 10. I can do them separately but cannot figure out how to combine the 2 formulas... =COUNTIF(range1, "internal") =COUNTIF(range2,"<=10")-COUNTIF(range2,"<6") Any help is greatly appreciated. . -- Dave Peterson |
|
#7
|
|||
|
|||
|
See:
http://tinyurl.com/6kyjd HTH Jason Atlanta, Ga -----Original Message----- What does the -- in front of the (range1 = "internal") do? "Meredith" wrote: Thank you! "Jason Morin" wrote: One way: =SUMPRODUCT(--(range1="internal"),--(range2=6),-- (range2<=10)) HTH Jason Atlanta, GA -----Original Message----- I would like to look at one column (range1) in excel and if the word "internal" appears, I would like to then look to the next column (range2) and count the total number of times the value is between 6 and 10... Internal 8 Internal 2 External 7 So in this data set, I would like my result to be equal to 1. Both internal and between 6 and 10. I can do them separately but cannot figure out how to combine the 2 formulas... =COUNTIF(range1, "internal") =COUNTIF(range2,"<=10")-COUNTIF(range2,"<6") Any help is greatly appreciated. . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using countif function to add only a half of a number | Excel Discussion (Misc queries) | |||
| Excel countif function | Excel Worksheet Functions | |||
| countif function?? | Excel Worksheet Functions | |||
| The countif function in Excel 2002. | Excel Worksheet Functions | |||
| hOW TO USE THE AND OPERATOR WITH THE COUNTIF FUNCTION | Excel Worksheet Functions | |||
Linear Mode
