Excel 2013 - Sum with a date range

Question

Good day, I have finally decided to ask for help. I have 1 workbook, multiple worksheets. The formula gives me #Value! which I am understanding that it has a wrong datatype. I am simply wanting look at a range within 1 column of dates, then add a column of numbers in 2 separate columns and divide those 2 columns and multiply by 30. It amounts to this. SUM(D31/C31)*30. This small formula works great when just using the numbers. I am taking total hours divided by amount of production multiplied by 30 dollars per hour. Obviously I will do this for each month of the year.

The 2 sheets are labeled as follows. This formula below resides on sheet "Monthly". It is referencing sheet "Overall Cost". I am hoping I have given you enough information that perhaps you might have the time to assist me. Note: I have the range pretty long because this is supposed to work for the whole year so I made it 10,000 rows deep.

Formula:

=SUMPRODUCT(('Overall Cost'!$B$3:$B$10000<=DATE(2015,9,30))*('Overall Cost'!$B$3:$B$10000>=DATE(2015,9,1)),SUM(('Overall Cost'!$D$3:$D$10000)/SUM('Overall Cost'!$C$3:$C$10000))*30)

Sample data:

               "B"      "C"    "D"
                       SQ FT   Hours
              Date       OH     OH
Wednesday   8/10/15     3427    232
Thursday    8/11/15     5536    232
Friday      8/12/15     2364    232
Monday      8/1/15      6408    232
Tuesday      9/4/15     2499    232
Wednesday    9/5/15     870     232

Is it just me or go none of the dates in your sample data meet the criteria in your formula? — user4039065
Sir, No you are correct. I just thru some dates in there as an example only. I will correct this so it doesnt confuse people. — CSMG

Unknown Unknown · Accepted Answer · 2015-09-30T15:01:11

You cannot use a SUM function within a SUMPRODUCT function like that. The SUMPRODUCT is trying to process each row by row and the SUM is totalling all of the rows into a single figure.

You are also going to get a number of #DIV/0! errors due to SUMPRODUCT's strong calculation mode. Your extra rows ranging down to 10000 can be truncated to the extent of the dates in column B. Since they are the criteria, it stands to reason that no numbers in column C and D would be significant in rows beyond the last date.

=SUMPRODUCT(('Overall Cost'!B$3:INDEX('Overall Cost'!B:B, MATCH(1E+99, 'Overall Cost'!B:B ))<DATE(2015, 10, 1))*
            ('Overall Cost'!B$3:INDEX('Overall Cost'!B:B, MATCH(1E+99, 'Overall Cost'!B:B ))>=DATE(2015, 9, 1)), 
             'Overall Cost'!$D$3:INDEX('Overall Cost'!D:D, MATCH(1E+99,'Overall Cost'!B:B)), 
          30/'Overall Cost'!$C$3:INDEX('Overall Cost'!C:C, MATCH(1E+99,'Overall Cost'!B:B )))

I've also adjust your maths hierarchy a bit. Multiplying the division of columns C and D was the same as multiplying column D by the fraction created by 30 over column C.

Providing you have no zero values in column C you should be alright. If you do then an IFERROR function or additional criteria may have to be brought in. This formula also limits calculation only to exactly what is needed with no blank 'safety-zone' of blank cells.

If yo are still receiving #DIV/0! errors then there must be blank cells or cells with 0 in column C. A #DIV/0! error is literally that; you are trying to divide a number by zero.

This can be solved with the IFERROR function but an extra layer of processing is required so you need to use Ctrl+Shift+Enter↵ to finalize the following formula.

=SUMPRODUCT((oc!B$3:INDEX(oc!B:B, MATCH(1E+99, oc!B:B ))<DATE(2015, 10, 1))*
               (oc!B$3:INDEX(oc!B:B, MATCH(1E+99, oc!B:B ))>=DATE(2015, 9, 1)),
             oc!$D$3:INDEX(oc!D:D, MATCH(1E+99,oc!B:B)),
             IFERROR(30/oc!$C$3:INDEX(oc!C:C, MATCH(1E+99,oc!B:B )), 0))

This can be mimicked with a non-CSE formula by forcing the numerator to zero and the denominator to one when encountering a #DIV/0! situation.

=SUMPRODUCT((oc!B$3:INDEX(oc!B:B, MATCH(1E+99, oc!B:B ))<DATE(2015, 10, 1))*
              (oc!B$3:INDEX(oc!B:B, MATCH(1E+99, oc!B:B ))>=DATE(2015, 9, 1))*
              (oc!$C$3:INDEX(oc!C:C, MATCH(1E+99,oc!B:B ))<>0),
             oc!$D$3:INDEX(oc!D:D, MATCH(1E+99,oc!B:B)),
         30/(oc!$C$3:INDEX(oc!C:C, MATCH(1E+99,oc!B:B ))+
              (oc!$C$3:INDEX(oc!C:C, MATCH(1E+99,oc!B:B ))=0)))

Excel 2013 - Sum with a date range

1 Answers