Counting by multiple criteria

[Guest]

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.

 

[Roger Govier]

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

 

[Ron Rosenfeld]

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

 

[Jerry W. Lewis]

=SUMPRODUCT((col2=date1)*(col3="Old"))

Jerry

 

[Guest]

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.