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
Who is Participating?
Try this:

kgerbChief EngineerCommented:
Is this what you're looking for?

Open in new window

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.

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.

While not specifically needed in this instance, I believe good practice would be to add the unary operator at the start:


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

Thanks for the learning.

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,
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).


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


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.



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.