Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
I want to count the number of record/rows that have a true condition when
column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
=SUMPRODUCT((A2:A8="apples")*(D2:D8="red"))
Tyro "Rae" wrote in message ... I want to count the number of record/rows that have a true condition when column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
In Excel 2007:
=COUNTIFS(A2:A8,"apples",D2:D8,"red") In Excel 2003: =SUM(IF(A2:A8="apples", IF(D2:D8="red",1,0), 0)) *enter this one as an array formula (instead of hitting enter, hit ctrl+shift+enter)* "Rae" wrote: I want to count the number of record/rows that have a true condition when column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
=SUM(IF(A2:A8="apples", IF(D2:D8="red",1,0), 0))
*enter this one as an array formula You'll get the same result and save a few keystrokes: (array enterd) =SUM((A2:A8="apples")*(D2:D8="red")) -- Biff Microsoft Excel MVP "Mike" wrote in message ... In Excel 2007: =COUNTIFS(A2:A8,"apples",D2:D8,"red") In Excel 2003: =SUM(IF(A2:A8="apples", IF(D2:D8="red",1,0), 0)) *enter this one as an array formula (instead of hitting enter, hit ctrl+shift+enter)* "Rae" wrote: I want to count the number of record/rows that have a true condition when column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
=COUNT(INDEX(SEARCH("apples",A2:A8)/(D2:D8="red"),0))
"Rae" wrote: I want to count the number of record/rows that have a true condition when column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
Good morning,
Thank you to everyone who responded. And all of these functions worked in test, except when I tried to use against real production data none of these functions worked for me. What I am trying to do is count records that meets the criteria that I am interrogating. The data that I am trying to interrogate exists within the same workbook as the calculation but in a seperate worksheet from the calculation. And I wish to set the calculation up to interrogate the entire column, so as the data grows the calculation does not have to be updated or changed because it should already be checking the entire column. See below: =SUMPRODUCT(('Data only'!A:A="apples")*('Data only'!G:G="red")) Does the data and calculation have to exist within the same worksheet to work? Thank you "Rae" wrote: I want to count the number of record/rows that have a true condition when column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
The formula can be on any worksheet. You don't state how the formula does
not work for you. Tyro "Rae" wrote in message ... Good morning, Thank you to everyone who responded. And all of these functions worked in test, except when I tried to use against real production data none of these functions worked for me. What I am trying to do is count records that meets the criteria that I am interrogating. The data that I am trying to interrogate exists within the same workbook as the calculation but in a seperate worksheet from the calculation. And I wish to set the calculation up to interrogate the entire column, so as the data grows the calculation does not have to be updated or changed because it should already be checking the entire column. See below: =SUMPRODUCT(('Data only'!A:A="apples")*('Data only'!G:G="red")) Does the data and calculation have to exist within the same worksheet to work? Thank you "Rae" wrote: I want to count the number of record/rows that have a true condition when column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
You can only use full column references in a SUMPRODUCT formula if you have
Excel 2007. For earlier versions you will have to refer to it as something like: =SUMPRODUCT(('Data only'!A2:A65536="apples")*('Data only'!G2:G65536="red")) The ranges do not have to be in the same worksheet. Hope this helps. Pete "Rae" wrote in message ... Good morning, Thank you to everyone who responded. And all of these functions worked in test, except when I tried to use against real production data none of these functions worked for me. What I am trying to do is count records that meets the criteria that I am interrogating. The data that I am trying to interrogate exists within the same workbook as the calculation but in a seperate worksheet from the calculation. And I wish to set the calculation up to interrogate the entire column, so as the data grows the calculation does not have to be updated or changed because it should already be checking the entire column. See below: =SUMPRODUCT(('Data only'!A:A="apples")*('Data only'!G:G="red")) Does the data and calculation have to exist within the same worksheet to work? Thank you "Rae" wrote: I want to count the number of record/rows that have a true condition when column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
Thank you!!!! The explanation of sumproduct limitations for earlier versions
of excel solved my problem. "Pete_UK" wrote: You can only use full column references in a SUMPRODUCT formula if you have Excel 2007. For earlier versions you will have to refer to it as something like: =SUMPRODUCT(('Data only'!A2:A65536="apples")*('Data only'!G2:G65536="red")) The ranges do not have to be in the same worksheet. Hope this helps. Pete "Rae" wrote in message ... Good morning, Thank you to everyone who responded. And all of these functions worked in test, except when I tried to use against real production data none of these functions worked for me. What I am trying to do is count records that meets the criteria that I am interrogating. The data that I am trying to interrogate exists within the same workbook as the calculation but in a seperate worksheet from the calculation. And I wish to set the calculation up to interrogate the entire column, so as the data grows the calculation does not have to be updated or changed because it should already be checking the entire column. See below: =SUMPRODUCT(('Data only'!A:A="apples")*('Data only'!G:G="red")) Does the data and calculation have to exist within the same worksheet to work? Thank you "Rae" wrote: I want to count the number of record/rows that have a true condition when column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to use to count value
You're welcome - glad to have helped.
Pete "Rae" wrote in message ... Thank you!!!! The explanation of sumproduct limitations for earlier versions of excel solved my problem. "Pete_UK" wrote: You can only use full column references in a SUMPRODUCT formula if you have Excel 2007. For earlier versions you will have to refer to it as something like: =SUMPRODUCT(('Data only'!A2:A65536="apples")*('Data only'!G2:G65536="red")) The ranges do not have to be in the same worksheet. Hope this helps. Pete "Rae" wrote in message ... Good morning, Thank you to everyone who responded. And all of these functions worked in test, except when I tried to use against real production data none of these functions worked for me. What I am trying to do is count records that meets the criteria that I am interrogating. The data that I am trying to interrogate exists within the same workbook as the calculation but in a seperate worksheet from the calculation. And I wish to set the calculation up to interrogate the entire column, so as the data grows the calculation does not have to be updated or changed because it should already be checking the entire column. See below: =SUMPRODUCT(('Data only'!A:A="apples")*('Data only'!G:G="red")) Does the data and calculation have to exist within the same worksheet to work? Thank you "Rae" wrote: I want to count the number of record/rows that have a true condition when column A and Column D equals "red apples"; how do I do this? Example: cell range a2 - a4 = apples cell range a5 - a6 = pears cell range a7 - a8 = apples AND cell range d2 = green cell range d3 - d5 = red cell range d6 - d7 = green cell range d8 = red I would expect to have a returned count value of 3 records/rows where data is "red apples".....make sense? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count If Function | Excel Worksheet Functions | |||
Count function | Excel Discussion (Misc queries) | |||
count function | Excel Discussion (Misc queries) | |||
Count Function | Excel Worksheet Functions | |||
count function | Excel Worksheet Functions |