I have two sheets: ‘Data’ and ‘Report’. In the report sheet, I need to calculate some data which requires 3 different formulae. The Data sheet has column headers in row 1 and many records of data in the rows below this. In the Report sheet, I need 3 formulas to calculate the following The number of records (rows) where column B of the Data sheet equals ‘Admission’, column C equals ‘HospA’, column D is not equal to blank (i.e. no value in cell), and where the value in Column A only occurs once in the column. The rows of data do not extend below 50000 rows. The number of records (rows) where column B of the Data sheet equals ‘Discharge’, column C equals ‘HospA’, column D is not equal to blank (i.e. no value in cell), and the different between two dates in columns E and F is between 0-2 weeks. For the third formula I need to return records that match the conditions as set out in the first formula requirement above, but rather than return the number of records, I need to return the actual value in Column A. More than 1 row would meet this criteria so I imagine that I would then copy this formula into adjacent cells to return the all the rows that match the conditions. I’ve been racking my head with these for a while, not getting very far. My attempts were to use SUM with nested IF for the first two formulas along the lines of... =SUM(IF(Data!B2:B50000="Admission",IF(Data!C2:C50000="HospA",IF(Data!D2:D50000<>"" ... from there I don’t know how to incorporate the last condition to only include records which have a unique numerical value in column A. Please help!
This question generated 11 answers. To proceed to the answers, click here.
This thread is current as of October 22, 2014.