Excel: Comparing dates in an Array Formula

1
votes

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.

2
exceldatecomparison-operatorsarray-formulas
Your formula doesn't work because 1) AND won't give you an array as an output, only a single value (TRUE or FALSE) and 2) you have a closing parenthesis in the wrong place. Jeeped has suggested a better alternative (IMO) but you can use * in your version to simulate AND and get the correct results, i.e. with this version =SUM(IF(($E$7:$E$200=$G$7:$G$200)*NOT(ISBLANK($G$7:$G$200)),1,0)) - confirmed with CTRL+SHIFT+ENTER – barry houdini
Hi Barry, @Jeeped I tried all solutions and all worked very well. Thank you for your assistance. vzmon1 – vzmon1

2 Answers

2
votes

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.

0
votes

Enter with CTRL + SHIFT + ENTER: 

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