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!
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!