Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to use to count instances of in Excel
I am trying to set up a formula that will count the instances of specific
dates. Example: Only count the number of times that a cell in Column B (B4=1) and a corresponding cell in Column C (C4=2) meet the required parameters. I have been trying to use the COUNTIF function but can not get it to work with the AND function since the AND function does not seem to work well with a range of data. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to use to count instances of in Excel
Try this:
A1: (some value) A2: (some other value) A3: =SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A2)) Counts the combinations of Col_A=A1 and Col_B=A2. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Bill" wrote: I am trying to set up a formula that will count the instances of specific dates. Example: Only count the number of times that a cell in Column B (B4=1) and a corresponding cell in Column C (C4=2) meet the required parameters. I have been trying to use the COUNTIF function but can not get it to work with the AND function since the AND function does not seem to work well with a range of data. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to use to count instances of in Excel
Bill, Try this. =SUMPRODUCT(--(ISNUMBER(FIND(DATE(2005,12,22),B1:B10))),--(C1:C10=DATE(2005,12,25))) Just change the dates in the DATE function to whatever dates you are looking for. Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=495488 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to use to count instances of in Excel
Ron's is definitely a simpler version. I just started experimenting with SUMPRODUCT so thanks Ron. I have seen this used many times on this site but don't fully understand what the -- that preceeds the conditions do. I've searched on the Help files with no luck. Any quick way to explain? Is there a site that you could suggest? Thanks, Happy Holidays. Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=495488 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to use to count instances of in Excel
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
-- Regards, Peo Sjoblom "SteveG" wrote in message ... Ron's is definitely a simpler version. I just started experimenting with SUMPRODUCT so thanks Ron. I have seen this used many times on this site but don't fully understand what the -- that preceeds the conditions do. I've searched on the Help files with no luck. Any quick way to explain? Is there a site that you could suggest? Thanks, Happy Holidays. Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=495488 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to use to count instances of in Excel
Thanks for the link! -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=495488 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I isolate my Excel server (automation) from other Excel instances? | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
In excel the formula bar behaves differently on different machines | Excel Discussion (Misc queries) | |||
Formula to make Excel count business days/skip weekends? | Excel Worksheet Functions |