1

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.

Any help would be much appreciated!

2

I'd use the SUMIFS and COUNTIFS formulas. For example:

=COUNTIFS(Data!B:B, "Admission", Data!C:C, "HospA", Data!D:D, "<>"&0)

The SUMIFS formula works slightly differently; you need to specify the column you are summing first and then start referencing your criteria columns and their corresponding criteria

=SUMIFS(Data!A:A, Data!B:B, "Admission", Data!C:C, "HospA",...etc)

extra:

for the difference (days) between two dates my suggestion would be to create another column measuring the difference and include the following:

=COUNTIFS(Data!G:G, "<="&14, ...etc)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.