Solved

# Count the number of records matching the criteria

Posted on 2011-10-14
Medium Priority
322 Views
Dear experts

Need a formulae in column F which will match the criteria in each of row under column A toD in this sheet with range of data in sheet 'data' column A to D and count the records which fit the criteria

the exepected results from the formula is given in range F3:f6

thank you
Count-the-number-of-records-matc.xls
0
Question by:Excellearner
• 4
• 2
• 2
• +2

LVL 12

Expert Comment

ID: 36971103
Is this what you're looking for?
``````=(Report!A3=data!A3)+(Report!B3=data!B3)+(Report!C3=data!C3)+(Report!D3=data!D3)
``````
Q-27397758.xls
0

LVL 24

Accepted Solution

StephenJR earned 1200 total points
ID: 36971113
Try this:

=SUMPRODUCT((data!\$A\$3:\$A\$15=Report!A3)*(data!\$B\$3:\$B\$15=Report!B3)*(data!\$C\$3:\$C\$15=Report!C3)*(data!\$D\$3:\$D\$15=Report!D3))
0

LVL 12

Expert Comment

ID: 36971140
Aaahah, good one.  Needed to see if the entry exists at all in the table and corresponding column.  Not a one for one match.  I couldn't figure out the pattern.

Kyle
0

LVL 42

Assisted Solution

dlmille earned 800 total points
ID: 36971758
While not specifically needed in this instance, I believe good practice would be to add the unary operator at the start:

=SUMPRODUCT(--(data!\$A\$3:\$A\$15=Report!A3)*(data!\$B\$3:\$B\$15=Report!B3)*(data!\$C\$3:\$C\$15=Report!C3)*(data!\$D\$3:\$D\$15=Report!D3))

Dave
0

LVL 85

Expert Comment

ID: 36978482
May I ask why? The unary operator adds nothing at all here that I can see - its only purpose is to convert TRUE/FALSE to 1/0 by performing a mathematical operation on them, but that is already achieved with the multiplication.
0

LVL 42

Expert Comment

ID: 36980755
Agreed, but if multiplication is not used, or only one set of parameters are used, then that effect doesn't happen.  Perhaps these are the only conditions where unary is required.  I have always put the unary in as a reminder that I wanted a count, as opposed to it being an effect of how the sumproduct was constructed.
0

LVL 85

Expert Comment

ID: 36980933
Doesn't make it good practice to add an unnecessary computation though! ;)
0

LVL 42

Expert Comment

ID: 36980963
Agreed - retracted.  I thought it would be a good practice, but if its adding computation, I retract that.

Thanks for the learning.

Dave
0

Author Comment

ID: 37267372
Hi StephenJr and DLmile,

Thank you for the formulaes.

I away on an unexpected long leave hence could not get back to you.

Both your formulae worked, but what i need to understand is what is the difference between your formulae and which is more robust.

Thank you,
0

LVL 42

Expert Comment

ID: 37267547
StephenJR's is the one to use.  There is no difference in output.  The unary -- I put in I always use because I've been too lazy to think about whether its needed or not.  However, you only need to use the -- double unary operatory if nothing else is being used to convert the TRUE/FALSES from the ARRAY comparison (e.g., A:A=something).

E.g.,

SUMPRODUCT((A:A=2)*(B:B)) <- Returns the sum of column B where rows in A column equal 2

SUMPRODUCT((A:A=2)) >- will not return the count of column A where rows in A equal 2 - the double unary is required
SUMPRODUCT(--(A:A=2))) <- Returns a count of column A where rows in A column equal 2
Also
SUMPRODUCT((A:A=2)*1) or SUMPRODUCT((A:A=2)+0) will also return count, because the *1 and +0 coerce the TRUE/FALSES to 1/0's and that's what gets added up

However

SUMPRODUCT((A:A=2)*(B:B=5)) or
SUMPRODUCT(--(A:A=2)*(B:B=5)) will both return the count when rows in column A equal 2 simultaneous with rows in column B = 5

Because (A:A=2)*(B:B=5) the multiplication already converts the TRUE/FALSE's from the first and second pair to 1's and 0's - hence the double unary -- is not needed, and apparently adds additional computation.

When coersion is needed to convert the TRUE/FALSE's to 1's or 0's, documentation I've read is that use of the double unary -- is marginally more efficient.

Cheers,

Dave

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diā¦
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilā¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month14 days, 17 hours left to enroll