Functions

[Cheryl]

Help please any suggestions would be appreciated. I am trying to produce a
complex spreadsheet refer a rough example:
TABLE 1:
A B C
1 12456 Service 05/01/08
2 58793 PRI 02/02/08
3 6549843 BSA 16/01/08
4 145648 Construction 12/06/08

TOTAL of column A but as a single added number: 4 (I thought countif might
be able to do this especially if I have blank cells in between but only count
the cell with text/numbers as 1 not the entire number e.g 12456)

Now the complex bit:-
TABLE 2:
A D E F
G
Service PRI BSA
Construction
12 January 1 1
13 February 1
14 March
15 etc
17 June
1
(NB: begining & end date period column hidden)
I need Table 2 to read the information as: column c (from Table 1) falls
between dates of January and is a "Service" (from Table 1column B) it will
show as a counted number under january so on and so forth.

I have the function of defining date period as true or false but to define
further as a number I am not sure of and to add the complexity of defining
further is beyond my knowledge

Currently using: =IF((C1>=(B12:C12),C1<=(B12:C12))

Once again any suggestions would be appreciated.

 

[T. Valko]

only count the cell with text/numbers as 1

Use the COUNTA function:

=COUNTA(A1:A10)

Now the complex bit:-
(NB: begining & end date period column hidden)
=IF((C1>=(B12:C12),C1<=(B12:C12))

Are your dates all within the same year?

Based on your formula it looks like B12:C12 = 1/1/2008 and 1/31/2008

If that's the case and all the dates are within the same year then you can
do this without the hidden dates.

Enter this formula in D12:

=SUMPRODUCT(--($C$1:$C$4=D$11),--(TEXT($D$1:$D$4,"mmmm")=$A12))

Copy across then down as needed.