Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting by multiple criteria
I think that my question is similar to Sami's, below, if so my apologies, but
I didn't understand the response. I need to count the number of entries that meet a set of criteria across multiple cells. For example: number date1 New Text Text Data1 number date1 Old Text Text Data1 number date2 Middle Text Text Data2 number date3 New Text Text Data2 number date1 Old Text Text Data1 I need to find out how many rows have date1, Old, Data1 in the appropriate fields (here, the result would be 2). The result will be sent to another worksheet and the source data has been exported from Access. I cannot sort the fields first as there will be multiple counts using different criteria and I am trying to automate the process. I do not need to know the actual data in any of the fields, just the number of records that match my criteria. Any suggestions would be much appreciated. |
#2
|
|||
|
|||
Hi Dave
Assuming the data is in columns A through F then =SUMPRODUCT(--($B$1:$B$100="date"),--($C$1:$C$100="old"),--($F$1:$F$100="Data1")) should do what you want. Change ranges to suit, but do ensure all range are of equal size. If you have problems with the date part, you might need to use ($B$1:$B$100=date(2005,9,28) as the date format for the relevant date. Regards Roger Govier Risky Dave wrote: I think that my question is similar to Sami's, below, if so my apologies, but I didn't understand the response. I need to count the number of entries that meet a set of criteria across multiple cells. For example: number date1 New Text Text Data1 number date1 Old Text Text Data1 number date2 Middle Text Text Data2 number date3 New Text Text Data2 number date1 Old Text Text Data1 I need to find out how many rows have date1, Old, Data1 in the appropriate fields (here, the result would be 2). The result will be sent to another worksheet and the source data has been exported from Access. I cannot sort the fields first as there will be multiple counts using different criteria and I am trying to automate the process. I do not need to know the actual data in any of the fields, just the number of records that match my criteria. Any suggestions would be much appreciated. |
#3
|
|||
|
|||
On Wed, 28 Sep 2005 03:56:02 -0700, "Risky Dave"
wrote: I think that my question is similar to Sami's, below, if so my apologies, but I didn't understand the response. I need to count the number of entries that meet a set of criteria across multiple cells. For example: number date1 New Text Text Data1 number date1 Old Text Text Data1 number date2 Middle Text Text Data2 number date3 New Text Text Data2 number date1 Old Text Text Data1 I need to find out how many rows have date1, Old, Data1 in the appropriate fields (here, the result would be 2). The result will be sent to another worksheet and the source data has been exported from Access. I cannot sort the fields first as there will be multiple counts using different criteria and I am trying to automate the process. I do not need to know the actual data in any of the fields, just the number of records that match my criteria. Any suggestions would be much appreciated. With your table in A1:F5 -- =SUMPRODUCT((B1:B5="date1")*(C1:C5="Old")*(F1:F5=" Data1")) --ron |
#4
|
|||
|
|||
=SUMPRODUCT((col2=date1)*(col3="Old"))
Jerry Risky Dave wrote: I think that my question is similar to Sami's, below, if so my apologies, but I didn't understand the response. I need to count the number of entries that meet a set of criteria across multiple cells. For example: number date1 New Text Text Data1 number date1 Old Text Text Data1 number date2 Middle Text Text Data2 number date3 New Text Text Data2 number date1 Old Text Text Data1 I need to find out how many rows have date1, Old, Data1 in the appropriate fields (here, the result would be 2). The result will be sent to another worksheet and the source data has been exported from Access. I cannot sort the fields first as there will be multiple counts using different criteria and I am trying to automate the process. I do not need to know the actual data in any of the fields, just the number of records that match my criteria. Any suggestions would be much appreciated. |
#5
|
|||
|
|||
"Risky Dave" wrote:
I think that my question is similar to Sami's, below, if so my apologies, but I didn't understand the response. I need to count the number of entries that meet a set of criteria across multiple cells. For example: number date1 New Text Text Data1 number date1 Old Text Text Data1 number date2 Middle Text Text Data2 number date3 New Text Text Data2 number date1 Old Text Text Data1 I need to find out how many rows have date1, Old, Data1 in the appropriate fields (here, the result would be 2). The result will be sent to another worksheet and the source data has been exported from Access. I cannot sort the fields first as there will be multiple counts using different criteria and I am trying to automate the process. I do not need to know the actual data in any of the fields, just the number of records that match my criteria. Any suggestions would be much appreciated. Gents, an elegant and effective solution - my thanks. Roger, I did have to use the date function as well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting using multiple criteria | Excel Discussion (Misc queries) | |||
Counting multiple cells using a criteria | Excel Discussion (Misc queries) | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions |