Help with three specific formulae to match and count several conditions

S

Satele

New Member
Joined
May 20, 2014
Messages
30
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
If i understand this problem correctly I think Sumproduct would be a better fit.
Given you want it to give count number of rows where certain criteria are met.

And if the value in Column A only occurs once, that would mean this row "build-up" only occurs once... OR, do you mean a unique, as in a specific value you set?

And would the sum of Column A values ("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.") be preferable? or just a "yes" or "no" where the criterias are met or not?
 
Upvote 0
To atleast answer this:

" 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."

=IF(COUNTIF(A2:A50000,A2)=1,"Unique","Not Unique")

That should do it, this is just an example, if you Drag this formula down, it will return "Unique" for the unique values.
And you can change theese "answers" to your liking.
 
Upvote 0
The values in column A are in the format 90010, 90011, 90012 etc. However, this value (which is unique to every person - not record), may appear in column A more than once.

For example, a sample of data may look like this:

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

I would want the formula to return a value of 1 here as it should only count the 3rd row in this example. This is because the person ID '90011' only occurs once in column A and the other 3 criteria in columns B, C, and D are also met.

The sum of the number of rows meeting the above criteria would be needed rather than the Yes/No.

Thanks
 
Upvote 0
An example of the sumproduct formula.

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

Replace "Testing" with whatever value you want, if you want to drag this formula down, and only use unique values, you can implement the countif from the previous post into this formula, and change the ranges accordingly. (here I would use an IF/AND statement)

PS:
And, there is a SUMIF(s) formula, you are currently using SUM and IF separately, there is a formula that does both in one, and already exist. :)
 
Upvote 0
Satele said:
The values in column A are in the format 90010, 90011, 90012 etc. However, this value (which is unique to every person - not record), may appear in column A more than once.

For example, a sample of data may look like this:

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

I would want the formula to return a value of 1 here as it should only count the 3rd row in this example. This is because the person ID '90011' only occurs once in column A and the other 3 criteria in columns B, C, and D are also met.

The sum of the number of rows meeting the above criteria would be needed rather than the Yes/No.

Thanks
Click to expand...

Ok, I see,

then I would use this formula on the Data sheet: (I used row number 9 in the example)

=IF(AND(D9<>"";C9="HospA";B9="Admission";COUNTIF(A:A;A9)=1);1;0)

This formula will return the value 1, if all crierias are met, meaning your row number three would return one.
If you sum up this column, you will have the sum of rows that are unique. with columns, B,C,D, criterias met.
 
Upvote 0
Hi,

Thanks for this. When I try to use this formula I am getting an error when I try to change the references from D9 to Data2!E1:E50000 and it won't let me proceed.
 
Upvote 0
In fact, it won't let me use the formula as entered either. Is there a mistake in the formula you've given above that I'm not seeing?

=IF(AND(D9<>"";C9="HospA";B9="Admission";COUNTIF(A:A;A9)=1);1;0)


 
Upvote 0
When using SUMPRODUCT ranges needs to be the same, meaning D3:D20 and E2:E20 wont work, you need E3:E20..

And I am using the european version of excel, so you might not have noticed the difference in dividing parts of a formula..

I use ; while you use comma, if you change that, it works :)
 
Upvote 0
Satele said:
Hi,

Thanks for this. When I try to use this formula I am getting an error when I try to change the references from D9 to Data2!E1:E50000 and it won't let me proceed.
Click to expand...

The reference cant be E1:E50000 in the IF(AND(... formula, the if and is a formula you will need to insert in its own column in sheet Data2!
Then sum up this column. Then you have the number you are looking for.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Returning Values Based on Referent Cells (Avoiding Redundant Values)
Replies
5
Views
254
Fluff
Fluff
V
  • Solved
Conditional Formatting Formula
Replies
2
Views
544
awoohaw
awoohaw
T
  • Solved
=IF Formula Question
Replies
5
Views
263
Fluff
Fluff
G
  • Solved
To accept manual data or return a default value based on condition.
2
Replies
16
Views
780
felixstraube
F
J
  • Solved
How to apply changing conditional formatting to an array of columns
2
Replies
12
Views
335
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,965
Messages
6,122,500
Members
449,090
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top