Hi,
I'm using SUMPRODUCT to sum a large number of rows based on 3 conditional columns with 3 inputs. When the conditional column value matches the input for that column, that row's value is included in the total. When the input cell is blank, I want all rows to be included for that condition (i.e. the array that is created for that condition should contain all "1"s).
I can't figure out how to force the array to be all "1" when the input cell is blank however. I've read some answers where people suggest an IF statement that checks of the input cell is blank, just do a sum on the entire column of numbers; if it is not blank, use SUMIFS or SUMPRODUCT to apply the conditions. This works fine for a single condition, but not for 3 conditions (you would need 8 nested IF statements and corresponding SUMPRODUCT statements).
Is there an easier way to force the array associated with each condition to populate with all "1"s if the condition input cell is blank?
Thanks in advance for any advice you can offer.
Bookmarks