Excel Magic Trick 1236: Count Numbers that Contain Whole Number 4: COUNTIFS or SUMPRODUCT?

Learn how to count numbers that contain the whole number 4:
1. (00:11) Problem Introduction
2. (01:05) COUNTIFS formula that uses Cell References, Comparative Operators and the Ampersand (Join Symbol)
3. (03:14) INT and SUMPRODUCT Array Formula with three array operations. Learn about how Excel calculates formulas. Learn about using Double Negative to convert TRUEs and FALSESs (Boolean Values) to ones and zeroes (1s and 0s).

[Sal_A]

For big spreadsheet better to use CountIfs, but for small spreadsheets and to win office prestige use Sumproduct with cool use of INT. Thanks Mike for all the hard work!

[OzduSoleilDATA]

I didn't know that about the cell reference turning black. That's REALLY good to know, and can help prevent getting in too deep with a formula that won't work.

[MySpreadsheetLab]

Nice use of the INT function Mike! Thanks for the video!

[BillSzysz]

Thanks Mike!! COUNTIFS rocks.
And now "The Dark Side of the Force"
=INDEX(FREQUENCY(Numbers, C2+{0, 1} -
Probably it will be faster than COUNTIFS but i'm not sure ;-)

[elphau]

as always, great video! amazing tutorial!

[Reduce_Scan]

Thank you for this trick
and this is anoher solution

[mikehdz]

I have data running from B7 to R60, in which I need only a count of numbers only, but the fields contain duplicate numbers and text as well... I need to count only one of the duplicates and no text... So I ended up with =SUMPRODUCT((B7:R60<>"")/COUNTIF(B7:R60, B7:R60&"")), but this is counting text as well, what criteria should I use for the formula not to count text and only numbers?

[MrSarky]

I first thought about counting the whole numbers. If someone is curious: