EDIT: Reading "not equal to" operator cannot be used with SUMIF. I’ll leave the question open in case someone comes with an alternate function to obtain the result I was looking for.
I have a formula that SUM hours worked in a month on two projects. In Sheet1, on each row, are the number of hours worked, in a month, on a specific task. Associated with the tasks are corresponding IDs. In Sheet2, some of the task IDs are listed in one of two columns, column A and column B, corresponding to ProjectA and ProjectB, so that for each project I have the list of tasks related to it. In Sheet3, the formula aggregates the hours worked on both projects, associated with multiple tasks, and excludes the hours worked on tasks not associated with a project. Does it makes sens? The formula looks like this:
=SUM(ARRAYFORMULA(SUMIF('Sheet1'!B5:B;"="&{Sheet2!A2:B};'Sheet1'!C5:C)))
This formula works just fine, and gives the following result:
80:45:00 (hours)
But when I try to aggregate the remaining worked hours, on tasks not associated with ProjectA or ProjectB, by using instead of the "equal to" operator (=) the "not equal to" operator (<>), to exclude hours worked on tasks which IDs are associated with either project, the resulting formula:
=SUM(ARRAYFORMULA(SUMIF('Sheet1'!B5:B;"<>"&{Sheet2!A2:B};'Sheet1'!C5:C)))
Does not provide the expected result at all.
I get:
167251:45:00
Instead of:
3:00:00 hours
(!)
Copy of the spreadsheet can be found here.
Help would be greatly appreciated as always.
Julien, try
=sum(arrayformula(if(COUNTIF(livrables!$A$2:$D;'résumé'!$B$5:$B);0;'résumé'!C$5:C)))
you can use the same formula for C7
=sum(arrayformula(if(COUNTIF(livrables!$A$2:$D;'résumé'!$B$5:$B);'résumé'!C$5:C;0)))
https://docs.google.com/spreadsheets/d/1h0daTbk3S8yIH48ijk250ZnxTIf7DoLO8SEyolaWrRs/edit?usp=sharing
Countif is used to compare the data, if they match I take the sum in C7, if they don't match I take the sum in C8, that's why 0 and sum are swapped.