Search code examples
excelsumproduct

Countifs/Sumproducts with or conditions and cell references/date references

Research of similar examples doesn't seem to yield results. Manually checking the table against formula results shows the wrong counts.

I have a table, QM10Data, with columns [Value Stream], and [DECN Date2]. [Value Stream] is made up of text entries, and [DECN Date2] are dates.

I'm trying to count all rows where [Value Stream] equals text stored in a cell reference B2, while also filtering [DECN Date2] for values greater than a date stored in cell C2 and any blank cells in [DECN Date2]. Any tips would be appreciated, thank you.

Solution

  • You could add another column, say, [count it], with the formula

    =IF(AND([Value Stream] = $B$2, OR(ISBLANK([DECN Date2]), [DECN Date2] > $C$2)), 1, 0)

    and then just sum up that new column. The summing would just count the rows with 1 in this new column and ignore those with 0, as adding zero does not change the result.