CODEWITHSUNDEEP
conditional-statementslibreoffice-calc
The Gugaru
The Gugaru

Reputation: 648

Complex conditional

I have the following sheet called DailyReport:

first example sheet

I am currently calculating Column M with:

=SUMPRODUCT(A2:A200=A2, G2:G200)

Then on a secondary sheet I have the following second sheet WeeklyReport:

second example sheet

Now what I want to do is, if WeeklyReport Column A2 == DailyReport Column A, then take the date in DailyReport Column B and test it to fall in the date range in WeeklyReport Column B and Column C with:

=IF(AND(DailyReport.B2>=B2,DailyReport.B2<=C2),1, 0)

and if that is true add the Total Daily Hours to the total in WeeklyReports Column D from DailyReports Column M.

Upvotes: 1

Views: 380

Answers (1)

Lyrl
Lyrl

Reputation: 935

I think summing values from column M, which is itself a sum, would not meet the goal. Summing sums would make too big a value, and column M is not filtered by date, so numbers from the wrong dates would be included.

I like better the idea of extending the way you used SUMPRODUCT to get the column M numbers. Instead of just checking for a matching name, add two more parameters to check for a date later or equal to the "Week Start Date" and earlier or equal to the "Week End Date".

So three true/false or 1/0 parameters (where multiplying by 1 for true keeps the value and multiplying by 0 for false removes the value) and the fourth parameter of the hour values to be summed: 

=SUMPRODUCT(DailyReport.A$2:A$200=A2, DailyReport.B$2:B$200>=B2, DailyReport.B$2:‌​B$200<=C2, DailyReport.G$2:G$200)

The poster also came very close to a solution using SUMIFS (in the comments). The default condition test is =, but for the date comparisons we want to use greater than and less than operators. The LibreOffice/OpenOffice syntax for this is to put the relational operators in double quotes, then use & to connect them to the cell address that contains the test value:

=SUMIFS(DailyReport.G$2:G$200, DailyReport.A$2:A$200, A2, DailyReport.B$2:B$200, ">=" & B2, DailyReport.B$2:B$200, "<=" & C2)

In both these cases I have included $ signs to make the row numbers absolute. Absolute cell addresses will not change if the formula is copy-pasted; in this case the copy-paste might be over multiple rows on the WeeklyReport sheet to get hour totals for multiple people.

Upvotes: 2

Related Questions