SUMPRODUCT (Liam's business name) can perform criteria-based calculations on closed files. Note SUMIF and SUMIFS do not handle leading zeroes in codes correctly where as SUMPRODUCT does.
I coerce TRUE and FALSE by multiplying by 1. =A1*1
You don't have to coerce them to use them as values. If you multiple by the cell it will automatically be coerced into one or zero.
If A1 contains TRUE then =10*A1 will return 10. If A1 contains FALSE then =10*A1 will return zero.
Hi Simon
Thanks for the article.
The MROUND function can be inconsistent. I wrote a blog post explaining the issue and the solution formula - link below.
https://a4accounting.com.au/the-problem-with-the-mround-function/
Regards Neale
Yes I also avoid linking between files and advise against it, but sometimes its unavoidable.
SUMPRODUCT also correctly handles leading zeroes in codes whereas SUMIFS doesn't - try it with 00123, 0123 and 123.
SUMPRODUCT is my favorite function.
Also SUMPRODUCT works on closed files whereas SUMIFS doesn't.
Comprehensive article.
When training I always share the Alt key shortcut to open the Data Validation dialog.
Alt a v v
pressed in sequence, not held down.
Regards
Neale
Hi Simon nice trick - the Edit Name image for SparkLineRange has $B1 rather than $B7 in the Refers To box. Regards Neale
My answers
SUMPRODUCT (Liam's business name) can perform criteria-based calculations on closed files. Note SUMIF and SUMIFS do not handle leading zeroes in codes correctly where as SUMPRODUCT does.
I coerce TRUE and FALSE by multiplying by 1.
=A1*1
You don't have to coerce them to use them as values. If you multiple by the cell it will automatically be coerced into one or zero.
If A1 contains TRUE then =10*A1 will return 10.
If A1 contains FALSE then =10*A1 will return zero.
Hi Simon
Thanks for the article.
The MROUND function can be inconsistent. I wrote a blog post explaining the issue and the solution formula - link below.
https://a4accounting.com.au/the-problem-with-the-mround-function/
Regards
Neale
Yes I also avoid linking between files and advise against it, but sometimes its unavoidable.
SUMPRODUCT also correctly handles leading zeroes in codes whereas SUMIFS doesn't - try it with 00123, 0123 and 123.
SUMPRODUCT is my favorite function.
Also SUMPRODUCT works on closed files whereas SUMIFS doesn't.
Hi Simon
Comprehensive article.
When training I always share the Alt key shortcut to open the Data Validation dialog.
Alt a v v
pressed in sequence, not held down.
Regards
Neale
Hi Simon nice trick - the Edit Name image for SparkLineRange has $B1 rather than $B7 in the Refers To box.
Regards
Neale