| Home |
| Search |
| Today's Posts |
|
|
|
#1
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 | |||
Hybrid Mode
