Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions
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. :-) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions
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. -- Biff Microsoft Excel MVP "Cheryl" wrote in message ... 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. :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
XL2003 FILTER FUNCTIONS VS. XL2007 FILTER FUNCTIONS | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions |