# 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
###### Who is Participating?

Commented:
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

Chief EngineerCommented:
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

Chief 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

Commented:
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

Commented:
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

Commented:
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

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

Commented:
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 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

Commented:
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.