How to define excel SUMIFS criteria considering any text and numbers

Question

My SUMIFS formula's requirements are dependent on a cell (let's say A1) that has data that has been validated by a list and altered by user selection. If a cell has text or a number that can be selected from a drop-down menu, the SUMIFS formula uses that information as a criterion to determine the sum that applies. I want the formula to sum everything without any conditions if the criteria cell is left empty. My issue is that when I inserted an IF condition like SUMIFS(sum-range, criteria range,IF(A1>"",A1,"*")) into the criteria field of the SUMIFS formula, Excel only took into account text values and ignored cells with numbers. In a nutshell, I want the SUMIFS formula to sum all values, including blank cells, integers, and texts if there is no selection in A1. How do we go about doing that?

Answer 1

It essentially ignores column C's missing entry and only considers the other two. ( Although the fourth criterion in your example formula is not obvious from your list, the procedure can be expanded to include as many criteria as you may have.

=SUMPRODUCT((IF(LEN(C2),(INDEX(Lists,,1)=C2),TRUE))*(IF(LEN(C3),(INDEX(Lists,,2)=C3),TRUE))*(IF(LEN(C4),(INDEX(Lists,,3)=C4),TRUE))*SumRange)

To make things easier to comprehend, I made a named range Lists that includes your sample range E2:H10 and labelled I2:I10 as SumRange. The first column in the range is shown by INDEX(Lists,,1). It's crucial that Lists and SumRange both include the same amount of rows.