Sum of a range with specific date

[kebabpete]

Hi,

I'm currently using the following formula to give me a total number of units for a given month (Named data range=DATE_2012 & Month=July), based on the sum of total units for each user within a department (Archive)...

=SUMPRODUCT((UNITS_2012)*(MONTH(DATE_2012)=7)*(DEPARTMENT_2012="Archive"))

Does anyone know what I would use to find the total number of units for a given day of the year based on the same criteria? i.e. Sum for 'Archive' on specific date.

DAY seems to only refer to a day within a month, and not a day within a year;
DAY360 seems to only return a day between 2 given dates;
DATE seems to return the MS date/time code but I can't see how to get this into the formula.

Any ideas?

Thanks.

 

[T. Valko]

Hi,

I'm currently using the following formula to give me a total number of units for a given month (Named data range=DATE_2012 & Month=July), based on the sum of total units for each user within a department (Archive)...

=SUMPRODUCT((UNITS_2012)*(MONTH(DATE_2012)=7)*(DEPARTMENT_2012="Archive"))

Does anyone know what I would use to find the total number of units for a given day of the year based on the same criteria? i.e. Sum for 'Archive' on specific date.

DAY seems to only refer to a day within a month, and not a day within a year;
DAY360 seems to only return a day between 2 given dates;
DATE seems to return the MS date/time code but I can't see how to get this into the formula.

Any ideas?

Thanks.

Maybe something like this...

A1 = some date
B1 = Archive

=SUMPRODUCT(UNITS_2012,--(DATE_2012=A1),--(DEPARTMENT_2012=B1))

 

[T. Valko]

Maybe something like this...

A1 = some date
B1 = Archive

=SUMPRODUCT(UNITS_2012,--(DATE_2012=A1),--(DEPARTMENT_2012=B1))

If you're using Excel 2007 or later you can use the SUMIFS function:

=SUMIFS(UNITS_2012,DATE_2012,A1,DEPARTMENT_2012,B1)

 

[kebabpete]

Perfect, thanks!!!

 

[T. Valko]

Perfect, thanks!!!

You're welcome. Thanks for the feedback!