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.
For more resources for Microsoft Excel:
This article includes the following tags:
- Microsoft Excel