Excel: Matching and counting records (rows) to multiple conditions and unique values

Question

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!

Answer 1

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)