Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIf Array not working
Hello.
I have two columns of data - Column B is dates formatted as date (i.e. 3/10/2007), and column C is times formatted as time (i.e. 6:00 AM). Problem I'm having is that when I run an array to search for values that meet my criteria in both columns, I get 0, where I know that there are matching values. I use the following array formula: {=SUM((B$1:B$100="3/10/2007")*(C$1:C$100="6:00 AM"))}. There are 7 entires that meet this particular criteria, however, the formula gives me 0. ANy assitance with tis is appreciated. Note that when I run seperate COUNTIF statements in each column, I get the desired result, but only for the values in one column - I did this as a test to ascertain if I was using the proper syntax. Thank you. NWO |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIf Array not working
Try something like:
=SUMPRODUCT( (B$1:B$100=DATE(2007,10, 3))*(C$1:C$100=TIME(6,0,0))) Doesn't need to be array entered. Assuming you really do have dates and times in columns B and C Regards Trevor "NWO" wrote in message ... Hello. I have two columns of data - Column B is dates formatted as date (i.e. 3/10/2007), and column C is times formatted as time (i.e. 6:00 AM). Problem I'm having is that when I run an array to search for values that meet my criteria in both columns, I get 0, where I know that there are matching values. I use the following array formula: {=SUM((B$1:B$100="3/10/2007")*(C$1:C$100="6:00 AM"))}. There are 7 entires that meet this particular criteria, however, the formula gives me 0. ANy assitance with tis is appreciated. Note that when I run seperate COUNTIF statements in each column, I get the desired result, but only for the values in one column - I did this as a test to ascertain if I was using the proper syntax. Thank you. NWO |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif, not working. | Excel Discussion (Misc queries) | |||
countif not working | Excel Discussion (Misc queries) | |||
quirky array not working | Excel Worksheet Functions | |||
Array formula not working | Excel Worksheet Functions | |||
Working with array equations | Excel Discussion (Misc queries) |