Hi - Dave here.
One limitation of the COUNTIF function is that it won't count numbers with leading zeros. For example, in the worksheet below we want to count codes equal to "009875". The correct result is 2, but COUNTIF returns 4:
This is a limitation of the COUNTIF function. A good solution is to use the SUMPRODUCT function instead. Read how and download the workbook here:
[
Download the workbook and read the full explanation]
Errata
Last week's email about using XLOOKUP to look up an entire column contained incorrect cell references. The correct references are in
red below:
How can you lookup an entire column with a formula?
The XLOOKUP function makes it really easy. For example, in the worksheet below, H4 contains the lookup value "Q3", and the formula in H5 is:
=XLOOKUP(H4,quarter,data)
This problem only affects the email. The
article and workbook are correct. Thanks to readers who pointed this out to me. Your comments about typos are always appreciated, and I do my best to fix problems within 24 hours.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
