Look Who it Was Random video

Excel Magic Trick 1171: SUMIFS & SUMIF Treat 0500 & 00500 the Same! Use SUMPRODUCT Instead

4,627 views · Published 15 January 2015 · 3:06 · Indexed 26 October 2026

Channel: excelisfun · 2015 · Education

Watch on YouTube

Download Excel Start File: https://excelisfun.net/files/EMT1171.xlsx

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

More from this channel