Excel Magic Trick 1171

SUMIFS & SUMIF Treat 0500 & 00500 the Same! Use SUMPRODUCT Instead

DESCRIPTION

See how to add for Text Number Criteria such as 0500 and 00500 using SUMPRODUCT rather than SUMIFS or SUMIF:
(00:08) Problem Setup: My Text Numbers with Lead Zeroes as Criteria are not working correctly in SUMIFS or SUMIF
(00:24) SUMIFS Function. SUMIFS seems to remove lead zeros, even if numbers are text.
(01:22) SUMPRODUCT Function, inclusing Double Negative to convert TRUEs and FALSEs to 1s and 02.
SUMIFS & SUMIF Treat 0500 & 00500 the Same!