Count the number of records matching the criteria

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
ExcellearnerAsked:
Who is Participating?
 
StephenJRCommented:
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
 
kgerbChief EngineerCommented:
Is this what you're looking for?
=(Report!A3=data!A3)+(Report!B3=data!B3)+(Report!C3=data!C3)+(Report!D3=data!D3)

Open in new window

Q-27397758.xls
0
 
kgerbChief EngineerCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dlmilleCommented:
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
 
Rory ArchibaldCommented:
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
 
dlmilleCommented:
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
 
Rory ArchibaldCommented:
Doesn't make it good practice to add an unnecessary computation though! ;)
0
 
dlmilleCommented:
Agreed - retracted.  I thought it would be a good practice, but if its adding computation, I retract that.

Thanks for the learning.

Dave
0
 
ExcellearnerAuthor Commented:
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
 
dlmilleCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.