Home 
Search 
Today's Posts 
#1




Complicated counting of cells (based on other cells contents)
Hi,
I'm looking to count the number of cells in a column based on the content in that cell and the content of another cell. Column 1 has three possible values (GGF, CGF, SGF) as text. Column 2 has two possible values (Yes, No) as text. Column 3 has two possible values (Pass, Fail) as text. I need to be able to make several counts based on the first column, referencing the other two columns. I'm going to use three different cells to display each count to simplify the formulas and to ease reading. Could someone please help me work the formula out to count the number of cells in column 1 with the value "GGF" when the value of column 2 is "No". I'd also like some pointers on how to work out how many cells have "GGF" and "Pass"/"Fail". The problem I'm hitting at the moment (I think) is that I need to be flexible (I don't know how many rows are in the column) and using $C:$C (for instance) seems to be messing up the count. I've tried: =COUNT(IF(($C:$C="GGF") & ($G:$G="No), 1, 0)) but that doesn't seem to cut the mustard so to speak. Any pointers would be a great help to someone in need of some brain soothing after trying to make Excel do this :) Thanks George 
#2




Complicated counting of cells (based on other cells contents)
modify as needed to get all your permutations
=SUMPRODUCT((A1:A1000="GGF"),(B1:B1000="No")) or, for all 3 columns =SUMPRODUCT((A1:A1000="GGF"),(B1:B1000="No"), (C1:c1000="Pass")) "George" wrote: Hi, I'm looking to count the number of cells in a column based on the content in that cell and the content of another cell. Column 1 has three possible values (GGF, CGF, SGF) as text. Column 2 has two possible values (Yes, No) as text. Column 3 has two possible values (Pass, Fail) as text. I need to be able to make several counts based on the first column, referencing the other two columns. I'm going to use three different cells to display each count to simplify the formulas and to ease reading. Could someone please help me work the formula out to count the number of cells in column 1 with the value "GGF" when the value of column 2 is "No". I'd also like some pointers on how to work out how many cells have "GGF" and "Pass"/"Fail". The problem I'm hitting at the moment (I think) is that I need to be flexible (I don't know how many rows are in the column) and using $C:$C (for instance) seems to be messing up the count. I've tried: =COUNT(IF(($C:$C="GGF") & ($G:$G="No), 1, 0)) but that doesn't seem to cut the mustard so to speak. Any pointers would be a great help to someone in need of some brain soothing after trying to make Excel do this :) Thanks George 
#3




Complicated counting of cells (based on other cells contents)
was does the  represent? I think this solves my prioblem also!
"Duke Carey" wrote: modify as needed to get all your permutations =SUMPRODUCT((A1:A1000="GGF"),(B1:B1000="No")) or, for all 3 columns =SUMPRODUCT((A1:A1000="GGF"),(B1:B1000="No"), (C1:c1000="Pass")) "George" wrote: Hi, I'm looking to count the number of cells in a column based on the content in that cell and the content of another cell. Column 1 has three possible values (GGF, CGF, SGF) as text. Column 2 has two possible values (Yes, No) as text. Column 3 has two possible values (Pass, Fail) as text. I need to be able to make several counts based on the first column, referencing the other two columns. I'm going to use three different cells to display each count to simplify the formulas and to ease reading. Could someone please help me work the formula out to count the number of cells in column 1 with the value "GGF" when the value of column 2 is "No". I'd also like some pointers on how to work out how many cells have "GGF" and "Pass"/"Fail". The problem I'm hitting at the moment (I think) is that I need to be flexible (I don't know how many rows are in the column) and using $C:$C (for instance) seems to be messing up the count. I've tried: =COUNT(IF(($C:$C="GGF") & ($G:$G="No), 1, 0)) but that doesn't seem to cut the mustard so to speak. Any pointers would be a great help to someone in need of some brain soothing after trying to make Excel do this :) Thanks George 
#4




Complicated counting of cells (based on other cells contents)
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html
 HTH RP (remove nothere from the email address if mailing direct) "ConstructionGuy" wrote in message ... was does the  represent? I think this solves my prioblem also! "Duke Carey" wrote: modify as needed to get all your permutations =SUMPRODUCT((A1:A1000="GGF"),(B1:B1000="No")) or, for all 3 columns =SUMPRODUCT((A1:A1000="GGF"),(B1:B1000="No"), (C1:c1000="Pass")) "George" wrote: Hi, I'm looking to count the number of cells in a column based on the content in that cell and the content of another cell. Column 1 has three possible values (GGF, CGF, SGF) as text. Column 2 has two possible values (Yes, No) as text. Column 3 has two possible values (Pass, Fail) as text. I need to be able to make several counts based on the first column, referencing the other two columns. I'm going to use three different cells to display each count to simplify the formulas and to ease reading. Could someone please help me work the formula out to count the number of cells in column 1 with the value "GGF" when the value of column 2 is "No". I'd also like some pointers on how to work out how many cells have "GGF" and "Pass"/"Fail". The problem I'm hitting at the moment (I think) is that I need to be flexible (I don't know how many rows are in the column) and using $C:$C (for instance) seems to be messing up the count. I've tried: =COUNT(IF(($C:$C="GGF") & ($G:$G="No), 1, 0)) but that doesn't seem to cut the mustard so to speak. Any pointers would be a great help to someone in need of some brain soothing after trying to make Excel do this :) Thanks George 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Clearing the Contents of Merged Cells  New Users to Excel  
Excel should have the option to merge contents when merging cells.  Excel Discussion (Misc queries)  
moving cells based certain value  New Users to Excel  
how to delete contents of cells having specific data  Excel Discussion (Misc queries)  
Counting rows based on criteria in multiple cells  Excel Discussion (Misc queries) 