ExcelBanter - Sum with IF and IF/Where

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Sum with IF and IF/Where (https://www.excelbanter.com/excel-discussion-misc-queries/230762-sum-if-if-where.html)

Sum with IF and IF/Where

Hi. I need to sum in excel some values where one of the criterias is the
Employee number and the other is if the Date column is equal or bigger the is
last entrance.

This is the look of my Employee sheet:
Employee Start
3000 05-01-2009

And I need to sum the values from this sheet:

Employee Date Time
3000 01-01-2009 1:00
3000 16-01-2009 8:00
3000 26-01-2009 0:30

I need to sum in order to get this:

Employee Time
3000 8:30

Can you help please?

Thanks,
Marco

Sum with IF and IF/Where

Marco wrote:
Hi. I need to sum in excel some values where one of the criterias is the
Employee number and the other is if the Date column is equal or bigger the is
last entrance.

This is the look of my Employee sheet:
Employee Start
3000 05-01-2009

And I need to sum the values from this sheet:

Employee Date Time
3000 01-01-2009 1:00
3000 16-01-2009 8:00
3000 26-01-2009 0:30

I need to sum in order to get this:

Employee Time
3000 8:30

Can you help please?

Thanks,
Marco

=SUMPRODUCT(--(Employee!A2=Values!$A$1:$A$9),--(Employee!B2<=Values!$B$1:$B$9),Values!$C$1:$C$9)

For much more on how and why this works see
http://xldynamic.com/source/xld.SUMPRODUCT.html