# Thread: Understanding Arrays

1. ## Understanding Arrays

Greetings!

I know I need to create an array formula, but i'm having some problems understanding the syntax.

I have a sheet, we'll call it sheet one. Sheet 1 has a series of ranges which represent call-center statistics. Sheet 2 is a culmination of the data on sheet one.

Sheet 1 has a column (A) which represents Firm/Client. As we only deal with either a firm or a client respectively, the properties of this column have been set to be either True, or False.

I have setup Column © as the date and time of the call, which is a mandatory field. So what I need, is a formula which will count any instance of "FALSE" or "TRUE" In column A on Sheet1, only if there is a value for date/time in Column C.

Any ideas?! )

Thanks!

Drk.

2. ## Re: Understanding Arrays

Hi Drk,
Not sure if you wanted to count those with false, those with true or those with either so:
To count those with false enter:
=SUM((A1:A6=FALSE)*(C1:C6<>"")) as an array by typing it in then pressing Control-Shift-Enter. To count true, just replace FALSE with TRUE! To count those with either (I'm assuming the other possibility is nothing in there) you'd use:
=SUM((A1:A6<>"")*(C1:C6<>"")) with Ctrl-Shift-Enter.
You can change the ranges as necessary.
Hope that helps.

3. ## Re: Understanding Arrays

Looks like that'll work.. Thanks again Rory! [img]/w3timages/icons/grin.gif[/img]

4. ## Re: Understanding Arrays

My pleasure [img]/w3timages/icons/grin.gif[/img]. Did you sort out that Range.Find problem? It occurs to me you could replace it with a For Each ...Next loop checking the Fomula of each cell as you go if the find didn't work.

5. ## Re: Understanding Arrays

Yeah, I sorted it out, it is still intermittent, so i'm not sure what's causing the problem, but i've added a 'goto' feature to get around that for now.. btw Rory, Thanks millions for all your help!

I've got another one which I think *may* apply,

I have 68 rows in a sheet, one column in these rows is 'time'. I want to return the number of times which occur in the 8 o'clock hour, 9 o'clock hour, 10 o'clock.... etc. any ideas?

6. ## Re: Understanding Arrays

Hi Drk,
You could use:
=SUM(IF(HOUR(A1:A68)=8,1,0)) entered as an array (i.e. Ctrl-Shift-Enter) changing the hour bit from 8 to whatever you need (and the range as necessary)
Always glad to help! [img]/w3timages/icons/grin.gif[/img]

7. ## Re: Understanding Arrays

Groovey.. How does it know the difference between 1am and 1pm? Thanks again!

Drk.

8. ## Re: Understanding Arrays

I'm assuming that the am/pm bit was specified on entry - Excel will translate 8pm as 20:00 for the purposes of its HOUR() calculation.

9. ## Re: Understanding Arrays

Ahh, so military time should be used to define the 'hours'.. Thanks Rory! [img]/w3timages/icons/smile.gif[/img]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•