Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mhall5
 
Posts: n/a
Default Counting Using Multiple Criteria


Does anyone know what is the best way to count values based on multiple
criteria? I am trying to count each instance where based on a certain
range of dates, a person name shows up. What would be the best way to
do this. I have tried countif, count(if(, Sumif, array formulas,
sumproduct,etc. All of these to no avail. If anyone has any suggestions
or thinks I am overlooking something please let me know. THe colums
which contain the dates and names of persons are not ajacent. Any name
can be corresponded with any date, even identical. Please help!!! Also
is it possible to enter date ranges as criteria in excel


--
mhall5
------------------------------------------------------------------------
mhall5's Profile: http://www.excelforum.com/member.php...o&userid=30087
View this thread: http://www.excelforum.com/showthread...hreadid=497726

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Counting Using Multiple Criteria

Hi!

Assumptions:

Dates in A1:A100
Names in Z1:Z100

is it possible to enter date ranges as criteria in excel


Use 2 cells to hold the date range and a cell to hold the name:

B1 = start date
C1 = end date
D1 = name

=SUMPRODUCT(--(A1:A100=B1),--(A1:A100<=C1),--(Z1:Z100=D1))

Biff

"mhall5" wrote in
message ...

Does anyone know what is the best way to count values based on multiple
criteria? I am trying to count each instance where based on a certain
range of dates, a person name shows up. What would be the best way to
do this. I have tried countif, count(if(, Sumif, array formulas,
sumproduct,etc. All of these to no avail. If anyone has any suggestions
or thinks I am overlooking something please let me know. THe colums
which contain the dates and names of persons are not ajacent. Any name
can be corresponded with any date, even identical. Please help!!! Also
is it possible to enter date ranges as criteria in excel


--
mhall5
------------------------------------------------------------------------
mhall5's Profile:
http://www.excelforum.com/member.php...o&userid=30087
View this thread: http://www.excelforum.com/showthread...hreadid=497726



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Counting Using Multiple Criteria

=SUMPRODUCT(--($A$2:$A$200=--"2006-01-01"),--($A$2:$A$200<=--"2006-01-31"),
--($X$2:$X$200="Pete"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mhall5" wrote in
message ...

Does anyone know what is the best way to count values based on multiple
criteria? I am trying to count each instance where based on a certain
range of dates, a person name shows up. What would be the best way to
do this. I have tried countif, count(if(, Sumif, array formulas,
sumproduct,etc. All of these to no avail. If anyone has any suggestions
or thinks I am overlooking something please let me know. THe colums
which contain the dates and names of persons are not ajacent. Any name
can be corresponded with any date, even identical. Please help!!! Also
is it possible to enter date ranges as criteria in excel


--
mhall5
------------------------------------------------------------------------
mhall5's Profile:

http://www.excelforum.com/member.php...o&userid=30087
View this thread: http://www.excelforum.com/showthread...hreadid=497726



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Elkar
 
Posts: n/a
Default Counting Using Multiple Criteria

This should get the results you want:

=SUMPRODUCT(--(A1:A100="Name"),--(C1:C100DATEVALUE("1/01/2005")),--(C1:C100<DATEVALUE("01/15/2005")))

Of course, adjust the ranges and dates to match your needs.

HTH,
Elkar

"mhall5" wrote:


Does anyone know what is the best way to count values based on multiple
criteria? I am trying to count each instance where based on a certain
range of dates, a person name shows up. What would be the best way to
do this. I have tried countif, count(if(, Sumif, array formulas,
sumproduct,etc. All of these to no avail. If anyone has any suggestions
or thinks I am overlooking something please let me know. THe colums
which contain the dates and names of persons are not ajacent. Any name
can be corresponded with any date, even identical. Please help!!! Also
is it possible to enter date ranges as criteria in excel


--
mhall5
------------------------------------------------------------------------
mhall5's Profile: http://www.excelforum.com/member.php...o&userid=30087
View this thread: http://www.excelforum.com/showthread...hreadid=497726


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mhall5
 
Posts: n/a
Default Counting Using Multiple Criteria


THanks for all the replies guys. I think that last one finally got it
for me. But I want to thank everyone who replied to my post.


--
mhall5
------------------------------------------------------------------------
mhall5's Profile: http://www.excelforum.com/member.php...o&userid=30087
View this thread: http://www.excelforum.com/showthread...hreadid=497726

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 by multiple criteria Risky Dave Excel Worksheet Functions 4 September 28th 05 01:29 PM
counting using multiple criteria SyntaX TerroR Excel Discussion (Misc queries) 3 August 25th 05 01:47 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 12:48 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"