Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Risky Dave
 
Posts: n/a
Default 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   Report Post  
Roger Govier
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

=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   Report Post  
Risky Dave
 
Posts: n/a
Default

"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
counting using multiple criteria SyntaX TerroR Excel Discussion (Misc queries) 3 August 25th 05 01:47 PM
Counting multiple cells using a criteria John Excel Discussion (Misc queries) 1 June 14th 05 04:51 PM
COUNTIF or SUMPRODUCT counting multiple criteria Kim Excel Worksheet Functions 1 June 1st 05 12:19 AM
COUNTIF or SUMPRODUCT counting multiple criteria Peo Sjoblom Excel Worksheet Functions 0 May 31st 05 11:40 PM
Counting "rows", i.e. simultaneous criteria for multiple cells gkline Excel Worksheet Functions 2 November 19th 04 08:30 AM


All times are GMT +1. The time now is 07:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"