Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting by multiple criteria | Excel Worksheet Functions | |||
counting using multiple 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 |