# Sumproduct,daterange,criteria

This is a discussion on Sumproduct,daterange,criteria within the Excel Questions forums, part of the Question Forums category; Can you please provide the actual result/number that you expect, based on the sample data that you provided, for the ...

1. ## Re: Sumproduct,daterange,criteria

Can you please provide the actual result/number that you expect, based on the sample data that you provided, for the following criteria...

Code:
```Column B:  GCE

StartDate:  6-Jul-12

FinishDate:  11-Jul-12```

2. ## Re: Sumproduct,daterange,criteria

Should equal to 13 only since it is only counting the cells that have a value from 6th july 2012 to 11 july 2012

3. ## Re: Sumproduct,daterange,criteria

If you're not getting the desired result it's likely because the ranges you're referencing is not correct....

A1:AC11:

 30-Jun 01-Jul 02-Jul 03-Jul 04-Jul 05-Jul 06-Jul 07-Jul 08-Jul 09-Jul 10-Jul 11-Jul 12-Jul 13-Jul 14-Jul 15-Jul 16-Jul 17-Jul 18-Jul 19-Jul 20-Jul 21-Jul 22-Jul 23-Jul ACME 03 PKD SHORT 30-Jun-12 08-Jul-12 74 74 74 74 74 74 74 ACME 03 PKD SHORT 01-Jul-12 09-Jul-12 75 75 75 75 75 75 ACME 12 GCE GROWTH 02-Jul-12 10-Jul-12 28 28 28 28 28 28 28 28 ACME 12 GCE GROWTH 03-Jul-12 11-Jul-12 50 50 50 50 50 50 ACME 34 GCE SHORT 04-Jul-12 12-Jul-12 53 53 53 53 53 53 ACME 34 SRJ SHORT 05-Jul-12 13-Jul-12 13 13 13 13 13 13 ACME 40 GYL SHORT 06-Jul-12 14-Jul-12 28 28 28 28 28 28 ACME 40 GYL SHORT 07-Jul-12 15-Jul-12 63 63 63 63 63 63 CQE 01 SRJ SHORT 08-Jul-12 16-Jul-12 76 76 76 76 76 76 76 76 CQE 03 SRE GROWTH 15-Jul-12 23-Jul-12 38 38 38 38 38 38 38

=SUMPRODUCT((\$F\$1:\$AC\$1>=DATE(2012,7,6))*(\$F\$1:\$AC\$1<=DATE(2012,7,11))*(\$B\$2:\$B\$11="GCE")*(ISNUMBER(\$F\$2:\$AC\$11)) )

or

=SUM(IF(\$F\$1:\$AC\$1>=DATE(2012,7,6),IF(\$F\$1:\$AC\$1<=DATE(2012,7,11),IF(\$B\$2:\$B\$11="GCE",IF(ISNUMBER(\$F\$2:\$AC\$11),1)))))

Note that the second formula needs to be confirmed with CONTROL+SHIFT+ENTER, and it should be more efficient. In both cases, the formulas return 13.

4. ## Re: 3daysnow,countcellswith values

I NEED TO COUNT THE CELLS THAT HAVE VALUES BETWEEN FEB 1 TO FEB 29 OF 2012 FOR ALL VALUES OF PCE IN COLUMN C

DOMINO formulas u gave me
does not work i tried both formulas and they both gave me "zero"
my table is really big i wish u can send me ur email so that i send it to u here is another table THESE 2 FORMULAS DO NOT WORK I THINK IT HAS SOMETHING TO DO WITH THE DATES DONT KNOW BEEN 3 DAYS NOW WORKING ON IT THE ANSWER FOR THE BELOW TABLE SHOULD BE 113

=SUM(IF(\$I\$6:\$US\$6>=DATE(2012,2,1),IF(\$I\$6:\$US\$6<=DATE(2012,2,29),IF(\$C\$7:\$C\$13="GCE",IF(ISNUMBER(\$I\$7:\$US\$13),1)))))

=SUMPRODUCT((\$I\$6:US6>=DATE(2012,2,1))*(\$I\$6:US6<=DATE(2012,2,29))*(\$B\$7:\$B\$13="pce")*(ISNUMBER(\$I\$7:US13)))

 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI . . . . . . . . . .. . . . . . . . . . . . . . . . . . . .. . .. . . . .. . . . . . . . UN UO UP UQ UR US 6 Resource Name Locations Calendar Project Type status feedback Spreadsheet Field 16-Jun 17-Jun 18-Jun 19-Jun 20-Jun 21-Jun 22-Jun 23-Jun 24-Jun 25-Jun 26-Jun 27-Jun 28-Jun 29-Jun 30-Jun 1-Jul 2-Jul 3-Jul 4-Jul 5-Jul 6-Jul 7-Jul 8-Jul 9-Jul 10-Jul 11-Jul 12-Jul 13-Jul 14-Jul 15-Jul 16-Jul 17-Jul 18-Jul 19-Jul 20-Jul 21-Jul 22-Jul 23-Jul 24-Jul 25-Jul 26-Jul 27-Jul 28-Jul 29-Jul 30-Jul 31-Jul 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug 9-Aug 10-Aug 11-Aug 12-Aug 13-Aug 14-Aug 15-Aug 16-Aug 17-Aug 18-Aug 19-Aug 20-Aug 21-Aug 22-Aug 23-Aug 24-Aug 25-Aug 26-Aug 27-Aug 28-Aug 29-Aug 30-Aug 31-Aug 1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep 7-Sep 8-Sep 9-Sep 10-Sep 11-Sep 12-Sep 13-Sep 14-Sep 15-Sep 16-Sep 17-Sep 18-Sep 19-Sep 20-Sep 21-Sep 22-Sep 23-Sep 24-Sep 25-Sep 26-Sep 27-Sep 28-Sep 29-Sep 30-Sep 1-Oct 2-Oct 3-Oct 4-Oct 5-Oct 6-Oct 7-Oct 8-Oct 9-Oct 10-Oct 11-Oct 12-Oct 13-Oct 14-Oct 15-Oct 16-Oct 17-Oct 18-Oct 19-Oct 20-Oct 21-Oct 22-Oct 23-Oct 24-Oct 25-Oct 26-Oct 27-Oct 28-Oct 29-Oct 30-Oct 31-Oct 1-Nov 2-Nov 3-Nov 4-Nov 5-Nov 6-Nov 7-Nov 8-Nov 9-Nov 10-Nov 11-Nov 12-Nov 13-Nov 14-Nov 15-Nov 16-Nov 17-Nov 18-Nov 19-Nov 20-Nov 21-Nov 22-Nov 23-Nov 24-Nov 25-Nov 26-Nov 27-Nov 28-Nov 29-Nov 30-Nov 1-Dec 2-Dec 3-Dec 4-Dec 5-Dec 6-Dec 7-Dec 8-Dec 9-Dec 10-Dec 11-Dec 12-Dec 13-Dec 14-Dec 15-Dec 16-Dec 17-Dec 18-Dec 19-Dec 20-Dec 21-Dec 22-Dec 23-Dec 24-Dec 25-Dec 26-Dec 27-Dec 28-Dec 29-Dec 30-Dec 31-Dec 1-Jan 2-Jan 3-Jan 4-Jan 5-Jan 6-Jan 7-Jan 8-Jan 9-Jan 10-Jan 11-Jan 12-Jan 13-Jan 14-Jan 15-Jan 16-Jan 17-Jan 18-Jan 19-Jan 20-Jan 21-Jan 22-Jan 23-Jan 24-Jan 25-Jan 26-Jan 27-Jan 28-Jan 29-Jan 30-Jan 31-Jan 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb 9-Feb 10-Feb 11-Feb 12-Feb 13-Feb 14-Feb 15-Feb 16-Feb 17-Feb 18-Feb 19-Feb 20-Feb 21-Feb 22-Feb 23-Feb 24-Feb 25-Feb 26-Feb 27-Feb 28-Feb 29-Feb 1-Mar 2-Mar 3-Mar 4-Mar 5-Mar 6-Mar 7-Mar 8-Mar 9-Mar 10-Mar 11-Mar 12-Mar 13-Mar 14-Mar 15-Mar 16-Mar 17-Mar 18-Mar 19-Mar 20-Mar 21-Mar 22-Mar 23-Mar 24-Mar 25-Mar 26-Mar 27-Mar 28-Mar 29-Mar 30-Mar 31-Mar 1-Apr 2-Apr 3-Apr 4-Apr 5-Apr 6-Apr 7-Apr 8-Apr 9-Apr 10-Apr 11-Apr 12-Apr 13-Apr 14-Apr 15-Apr 16-Apr 17-Apr 18-Apr 19-Apr 20-Apr 21-Apr 22-Apr 23-Apr 24-Apr 25-Apr 26-Apr 27-Apr 28-Apr 29-Apr 30-Apr 1-May 2-May 3-May 4-May 5-May 6-May 7-May 8-May 9-May 10-May 11-May 12-May 13-May 14-May 15-May 16-May 17-May 18-May 19-May 20-May 21-May 22-May 23-May 24-May 25-May 26-May 27-May 28-May 29-May 30-May 31-May 1-Jun 2-Jun 3-Jun 4-Jun 5-Jun 6-Jun 7-Jun 8-Jun 9-Jun 10-Jun 11-Jun 12-Jun 13-Jun 14-Jun 15-Jun 16-Jun 17-Jun 18-Jun 19-Jun 20-Jun 21-Jun 22-Jun 23-Jun 24-Jun 25-Jun 26-Jun 27-Jun 28-Jun 29-Jun 30-Jun 1-Jul 2-Jul 3-Jul 4-Jul 5-Jul 6-Jul 7-Jul 8-Jul 9-Jul 10-Jul 11-Jul 12-Jul 13-Jul 14-Jul 15-Jul 16-Jul 17-Jul 18-Jul 19-Jul 20-Jul 21-Jul 22-Jul 23-Jul 24-Jul 25-Jul 26-Jul 27-Jul 28-Jul 29-Jul 30-Jul 31-Jul 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug 9-Aug 10-Aug 11-Aug 12-Aug 13-Aug 14-Aug 15-Aug 16-Aug 17-Aug 18-Aug 19-Aug 20-Aug 21-Aug 22-Aug 23-Aug 24-Aug 25-Aug 26-Aug 27-Aug 28-Aug 29-Aug 30-Aug 31-Aug 1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep 7-Sep 8-Sep 9-Sep 10-Sep 11-Sep 12-Sep 13-Sep 14-Sep 15-Sep 16-Sep 17-Sep 18-Sep 19-Sep 29-Jun 30-Jun 1-Jul 2-Jul 3-Jul 4-Jul 5-Jul 6-Jul 7-Jul 8-Jul 9-Jul 10-Jul 11-Jul 12-Jul 13-Jul 14-Jul 15-Jul 16-Jul 17-Jul 18-Jul 19-Jul 20-Jul 21-Jul 22-Jul 23-Jul 24-Jul 25-Jul 26-Jul 27-Jul 28-Jul 29-Jul 30-Jul 31-Jul 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug 9-Aug 10-Aug 11-Aug 12-Aug 13-Aug 14-Aug 15-Aug 16-Aug 17-Aug 18-Aug 19-Aug 20-Aug 21-Aug 22-Aug 23-Aug 24-Aug 25-Aug 26-Aug 27-Aug 28-Aug 29-Aug 30-Aug 31-Aug 1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep 7-Sep 8-Sep 9-Sep 10-Sep 11-Sep 12-Sep 13-Sep 14-Sep 15-Sep 16-Sep 17-Sep 18-Sep 19-Sep 5-Dec 6-Dec 7-Dec 8-Dec 9-Dec 17-Dec 18-Dec 19-Dec 20-Dec 21-Dec 20-Dec 21-Dec 7 JND 04 PCE Wet Weather Continuous Day Night GROWTH out y Budgeted Units 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 8 JND03 PCE Wet Continuous Day GROWTH in y Budgeted Units 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 102 9 9 0 8 8 8 8 8 9 JND11 PCE Wet Weather Continuous Day Night GROWTH in y Budgeted Units 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 10 Nitro 02 PCE Wet Continuous Day GROWTH in y Budgeted Units 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 90 11 Nitro 02 PCE Wet Continuous Day GROWTH in y Budgeted Units 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 12 Nitro 07 PCE Wet Continuous Day GROWTH in y Budgeted Units 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 13 13 Nitro 14 PCE Wet Weather Continuous Day Night GROWTH in y Budgeted Units 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 0

5. ## Re: Sumproduct,daterange,criteria

Here are a few things to check for...

1) Make sure that the dates are true date values. So, for example, the formula formula should return TRUE...

=ISNUMBER(I6)

Are the dates true date values?

2) Make sure that cells in C7:C13 do not contain extra spaces.

3) Make sure that the numbers in I7:US13 are true numerical values, and not numbers formatted as text. So, again, for example, let's say I7 contains a number, the following formula should return TRUE...

=ISNUMBER(I7)

Are the numbers true numerical values?

6. ## Re: Sumproduct,daterange,criteria

thanks domino it was the format problem its working now but i still need to input a start and finish date instead of DATE(2012,2,1) i have a big table
so i want to give it a start and finish date rather than having it in the formula

=SUMPRODUCT((\$I\$6:US6>=DATE(2012,2,1))*(\$I\$6:US6<=DATE(2012,2,29))*(\$C\$7:\$C\$13="pce")*(ISNUMBER(\$I\$7:US13)))

THANKS DOMINO UR THE BEST !!!!!!!!!

7. ## Re: Sumproduct,daterange,criteria

Enter a start date in one cell, an end date in another cell, and "PCE" in yet another cell. Then refer to those cells in your formula. So, for example, let A2 contain the start date, B2 contain the end date, and C2 contain "PCE". Then try...

=SUMPRODUCT((\$I\$6:\$US\$6>=A2)*(\$I\$6:\$US\$6<=B2)*(\$C\$7:\$C\$13=C2)*(ISNUMBER(\$I\$7:\$US\$13)))

8. ## Re: Sumproduct,daterange,criteria

2 easy thanks dominic im planning on finishing my reporting system by wednesday i thank u soo much for ur help

God bless u!

9. ## Re: Sumproduct,daterange,criteria

You're very welcome. Glad I could help.

Cheers!

Page 2 of 2 First 12