CODEWITHSUNDEEP
excelspreadsheetexcel-2010
Alby
Alby

Reputation: 5742

sumif some columns value based on other column

I created the time log for what I do: To better illustrate, I attached the image as follows:

enter image description here

where start and end are the time when I started the particular task in the column A, and delta is the difference between the values in End and Start.

In the next sheet, I created the the summary of how much time I spent on each criteria like this:

enter image description here

Now the problem I had is:

1) The formula I have in the hour field in the summary sheet is this:

=sumif('Time spent'!$A$2:$A$100,A3,'Time spent'!$D$2:$D$100)

How do I make it so that values are up for the entry put in today? The reason why I want this feature is so that I can see my performance for today.

I have this on Google Doc, but I also would like to know how to get this done in Excel as well

Upvotes: 1

Views: 2904

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

In Excel, starting in version 2007 you can use the SUMIFS function - which allows multiple conditions. In your case, the syntax would be:

=SUMIFS('Time spent'!$D$2:$D$100,'Time spent'!$A$2:$A$100,A3,'Time spent'!$E$2:$E$100,TODAY())

In Excel 2003, you can do this with a SUMPRODUCT:

=SUMPRODUCT('Time spent'!$D$2:$D$100*('Time spent'!$A$2:$A$100=A3)*('Time spent'!$E$2:$E$100=TODAY()))

Upvotes: 3

Related Questions