This article and video will demonstrate two alternative approaches, using Excel SUMIFS function, to sum a range and ignore blank, text or error cells. Although there are a myriad of other solutions, this article will focus solely on these two SUMIFS examples.
This is a follow-up to a similar article on reverse VLOOKUP options. There will be instances where a reverse HLOOKUP, like a VLOOKUP, is required in a financial model. The following feature will outline two alternate approaches, which enable a form of reverse HLOOKUP.
Excel VLOOKUP is one of the most ubiquitous spreadsheet functions. Seemingly, an equivalent method to a reverse VLOOKUP will be required in some cases, because a standard VLOOKUP is unable to perform a reverse lookup. This article and video will explore a couple of alternative methods to a reverse VLOOKUP.
Here is a great approach to create ordinal numbers, which is dynamic and VBA macro-free. It applies Excel conditional formatting to the required data array. The ordinal number formatting will preserve the integer properties of the relevant data array, and will require a number of rules to be created.