COUNTIF / SUMPRODUCT- multiple criteria - including date range

**Sph01** · 08-29-2012, 11:41 AM

I would like a cell to display total sales data for a particular type of work, during a specified date range.

e.g. Total sales for "SMW" during July 2012

The 'Type' is column 7. The 'Job Value' is column 10. The 'Completion Date' is column 13.

I know this might be able to be achieved with SUMPRODUCT function, but as I will be collating monthly figures for multiple periods, I would like to avoid using target cells on another part of the sheet, with the date ranges in them.

I should mention that the cell which I would like to display this data is on another sheet to the target data.

Any assistance greatly appreciated

**NBVC** · 08-29-2012, 11:48 AM

I am not sure what this means?

I know this might be able to be achieved with SUMPRODUCT function, but as I will be collating monthly figures for multiple periods, I would like to avoid using target cells on another part of the sheet, with the date ranges in them.

incidently you can use SUMIFS too, which is better

e.g.

=SUMIFS(Sheet2!M:M,Sheet2!G:G,A1,Sheet2!J:J,B1)

where Sheet2 is your target sheet, and A1 and B1 contain the lookup Type and date, respectively...

or

=SUMIFS(Sheet2!M:M,Sheet2!G:G,A1,Sheet2!J:J,">="&B1,Sheet2!J:J,"<="&C1)

where Sheet2 is your target sheet, and A1 and B1 and C1 contain the lookup Type and start/end dates, respectively...

**Sph01** · 08-29-2012, 12:07 PM

Thanks for the speedy reply.

Originally Posted by NBVC

I am not sure what this means?

I meant that I want the formula to include the date ranges, rather than specifying them in a 'hidden' cell somewhere in the sheet - a method I have seen demonstrated elsewhere on Forums - but your solution has done this anyway.

However, the solution seems not to have worked.

I notice that you have referenced the range M:M at the start of the formula, but this is the column with the date in. J:J is the 'Value' column. Despite rearranging these, i still cant get it to work.

Any further suggestions?[COLOR="Silver"]

**NBVC** · 08-29-2012, 12:11 PM

So rearranging the Ms and Js didn't do it? The summing range is first range in the SUMIFS....

**Sph01** · 08-31-2012, 04:54 AM

My mistake - the formula does work: I rearranged the Ms and Js, and inserted "" around the start / end date values in the formula.

So it now reads :

=SUMIFS('Job Sheet'!J:J,'Job Sheet'!G:G,"SMW",'Job Sheet'!M:M,">="&"01/8/2012",'Job Sheet'!M:M,"<="&"31/8/2012")

Thanks for the help