CODEWITHSUNDEEP
exceldatecomparison-operatorsarray-formulas
vzmon1
vzmon1

Reputation: 69

Excel: Comparing dates in an Array Formula

I'm attempting to compare two dates to determine if the tasks were completed on time.

The column range is from E7:E200 for DUE and G7:G200 for COMPLETE date; both columns are formatted the same. I don't want the blank cells to be counted.

I crafted the following array formula:
{=SUM(IF(AND($E$7:$E$200=$G$7:$G$200, NOT(ISBLANK($G$7:$G$200),1,0)))))}

Excel doesn't throw any errors and neither does it give me the summation when I enter test data. If I use the same formula just 2 cells (E7 & G7). it works.

Greatly appreciate any help you can provide.

Upvotes: 1

Views: 2228

Answers (2)

user4039065
user4039065

Reputation:

A standard (non-array) formula would be,

=SUMPRODUCT(($E$7:$E$200=$G$7:$G$200)*($G$7:$G$200<>""))

While the SUMPRODUCT function produces array-type processing you do not have to enter it with Ctrl+Shift+Enter.

Upvotes: 2

rwilson
rwilson

Reputation: 2135

Enter with CTRL + SHIFT + ENTER: 

=SUM(IF($E$7:$E$200=$G$7:$G$200,IF($G$7:$G$200<>"",1)))

Upvotes: 0

Related Questions