OR logic in Sumproduct function in Excel

OR logic in Sumproduct function in Excel Sumproduct usually will be applied with AND logic. OR logic in sumproduct function can be implemented as detailed below. A) AND condition example is as follows: =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target A")) =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target B")) B) There are ways to add OR condition using “+” symbol. The “plus sign” (+) is for OR in...

Display Times as Hours/Minutes in Excel

Display Times as Hours/Minutes in Excel Usually, Excel stores time as decimal values and displaying times as hours or minutes. Ex: A1=200 B1=A1/1440 (1440 = 24*60) = 0.1389 Now, use custom format as h:mm (or) h” hours and “m” minutes” There is an another option in Excel using Convert function.  =Convert(A1,”mn”,”hr”) -Ref: https://www.accountingweb.com/technology/excel/display-times-as-hoursminutes-in-excel

Minimum if multiple criteria using Excel

Minimum if multiple criteria using Excel Get the minimum value by checking multiple criteria from different columns. Minimum if multiple criteria is calculated with the following array formulas including nested if conditions. A) The following formula checks different criteria and get the minimum values from value column. =MIN(IF(rng1=criteria1,IF(rng2=criteria2,values))) Press Ctrl+Shift+Enter to convert to array...

Extract phone numbers using Regex in Excel vba

Extract phone numbers using Regex in Excel vba Extract phone numbers from Excel using Regular expressions in the patterns (***) ***-****. In order to extract phone numbers, where cells are mixed with strings and phone numbers; Regular expression concept play an important role.   Option Explicit Sub ewqre() Dim str As String, n As...

Delete First line of Text file and Import -Excel vba

Delete First line of Text file and Import -Excel vba Select Folder that contains the text files. It reads each text file into an array and then builds a new array (that doesn’t include the first line of each text file) first way (not verified):  Sub SO() Const fileLocation As String = “C:\Users\BloggsJ\Folder\textFile.txt” Dim...

This is a demo store for testing purposes — no orders shall be fulfilled. Dismiss