Excel: Comparing dates in an Array Formula

Question

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.

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
– barry houdini, Commented Jul 16, 2015 at 12:07
Hi Barry, @Jeeped I tried all solutions and all worked very well. Thank you for your assistance. vzmon1 — vzmon1
– vzmon1, Commented Jul 17, 2015 at 12:00

score 2 · Accepted Answer · 2015-07-15 22:53:09Z

2

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.

edited Jul 15, 2015 at 22:53

answered Jul 15, 2015 at 22:51

user4039065

Sign up to request clarification or add additional context in comments.

Comments

rwilson · Accepted Answer · 2015-07-15 22:50:54Z

0

Enter with CTRL + SHIFT + ENTER:

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

answered Jul 15, 2015 at 22:50

rwilson

2,14513 silver badges18 bronze badges

1 Comment

vzmon1 Over a year ago

Hi rwilson: The system wouldn't allow me to include you in my response, but I wanted to thank you for your support.

Collectives™ on Stack Overflow

Excel: Comparing dates in an Array Formula

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related