https://aznot.com/index.php?action=history&feed=atom&title=Excel
Excel - Revision history
2026-04-28T15:26:12Z
Revision history for this page on the wiki
MediaWiki 1.41.0
https://aznot.com/index.php?title=Excel&diff=5703&oldid=prev
Kenneth: /* Count if Checked */
2020-08-23T19:23:31Z
<p><span dir="auto"><span class="autocomment">Count if Checked</span></span></p>
<p><b>New page</b></p><div>== Interest Rates ==<br />
<br />
https://i.imgur.com/OeOvmHV.png<br />
<br />
== Value in Range ==<br />
<br />
=IF(COUNTIF(range,value),"Yes","No")<br />
<br />
=IF(COUNTIF(range,value)>1,"Yes","No")<br />
<br />
ref: [https://exceljet.net/formula/value-exists-in-a-range]<br />
<br />
== cell contains substring ==<br />
<br />
=ISNUMBER(SEARCH("Some Text", A5))<br />
<br />
=if(ISNUMBER(search(J4,K4)),"","BAD")<br />
<br />
<br />
ref: [https://stackoverflow.com/questions/18617175/check-whether-a-cell-contains-a-substring]<br />
<br />
== Convert unix timestamp to date time string ==<br />
<br />
A1 = unix time stamp<br />
B1 = 25569 epoc shift (days since 1/1/1900 and 1/1/1970<br />
="1/1/1970"-"1/1/1900"+1<br />
C1 = -6 hours away from GMT<br />
= ( A1 / 1000 + B1 * 86400 + C1 * 3600 ) / 86400<br />
<br />
Sample:<br />
1537822800000 -> 09-24-18 15:00:00<br />
<br />
== Count if Checked ==<br />
<br />
=SUMPRODUCT(($D$2:$D$15=TRUE)*$C$2:$C$15)<br />
<br />
=SUMPRODUCT(($D$2:$D$15="X")*$C$2:$C$15)<br />
Column D has "X"<br />
Column C has $ values<br />
<br />
https://www.extendoffice.com/documents/excel/2460-excel-sum-count-checkboxes.html<br />
<br />
== Split by delimiter ==<br />
<br />
Split a single column that has a delimiter<br />
"Smith, John"<br />
<br />
=LEFT(A2, SEARCH(",",A2,1)-1) # Smith<br />
=RIGHT(A2, LEN(A2)-SEARCH(",",A2,1)-1) # John<br />
<br />
<br />
Ref [https://www.ablebits.com/office-addins-blog/2016/06/01/split-text-string-excel/]<br />
<br />
== keywords ==</div>
Kenneth