Help with three specific formulae to match and count several conditions

[Satele]

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!

 

[Arithos]

I have one solution for you tho, it has some restrictions to it, regarding the data.

A B C D
90010 Admission HospA 29/11/2012 X
90010 Discharge HospA 30/11/2012 X
90011 Admission HospA 12/04/2014
90013 Discharge HospA 04/03/2013

As long as there are a maximun of two similar numbers in column A after eachother, this formula will do the job.

=SUMPRODUCT(--(Data!B2:B50000="Admission")*--(Data!C2:C50000="HospA")*(Data!D2:D50000<>"")*--(Data!A2:A50000=Data!A1:A49999))

Try it out, This formula could be placed anywhere

 

[Satele]

Hi,

I can't get the SUMPRODUCT formula to work. Perhaps this is because in the Data2! sheet, I do have more than two instances of the same value in column A. Sometimes, the same value occurs up to 10 times in this column.

Can this formula be amended to work across the row range 2:50000?

=IF(AND(E2<>"",B2="Initial Assessment",COUNTIF(A:A,A2)=1),1,0)

 

[Arithos]

Hi,

I can't get the SUMPRODUCT formula to work. Perhaps this is because in the Data2! sheet, I do have more than two instances of the same value in column A. Sometimes, the same value occurs up to 10 times in this column.

Can this formula be amended to work across the row range 2:50000?

=IF(AND(E2<>"",B2="Initial Assessment",COUNTIF(A:A,A2)=1),1,0)

The IF statement can not (to my knowledge) be adjusted to go across a range like that, you will have to place it in individual cells.

The sumproduct assumed that there would only be 2 similar values in a row. If this is not the case my knowledge of formulas and getting it to work is at an end The problem arises when you do not want these counted.

However, this is solvable with VBA.