2 table database count if both databases have a specific range

[ohmedic88]

I need a formula that compares 2 databases and returns a count if both values or within the specific range.

Column A is titles Column B is values The first database is simple the range is <= 0.75
However my second database the value is in between positive 0.5 and Negative 0.5

I need a total count and a count that meets both Database 1 and Database 2 This way I can make a Percentage. So in the below example the total count would be 3 while the Eric and Todd meet both criteria. Emily does not have a value so would not be counted. Greg Fails the first database but passes the second. So on this example I would have a final answer of 66%

Database One <=75
A B
Eric 0.75
Greg 0.76
Todd 0.55
Emily

Database Two +or- .5
A B
Eric 0.5
Greg -0.4
Todd -0.1
Emily

Thanks in advance

 

[Jerry Sullivan]

Hi and Welcome to the Board,

If your two Databases have the same items in the same order as in your example then you could use the COUNTIFS function as shown below. (If you aren't using Excel 2007 or later, a similar formula with SUMPRODUCT could be used).

Excel Workbook
	A	B	C	D	E
1	Total Count	3
2	Meets Criteria	2
3	Ratio	66.7%
4
5
6	Db1 Criteria			Db2 Criteria
7
8				>=-0.5
9
10	Database 1			Database 2
11	Eric	0.75		Eric	0.5
12	Greg	0.76		Greg	-0.4
13	Todd	0.55		Todd	-0.1
14	Emily			Emily
Sheet

A formula approach becomes much more complex if the two Databases don't have all the same items in the same order.

In that case, consider Joining the two Databases into a third table aligns the data using unique IDs (Titles in this example). Once joined, the formulas above could be applied.

 

[ohmedic88]

Yes, That works really great... Thank you I greatly appreciate it.

Eric