Formula for SUMIFS to calculate difference between 2 collumns

[Joris Hermans]

Hello there you all.

I've been cruisin' the internet for a solution to my problem, but didn't find something usefull. I'm quiete sure someone at Chandoo forum can (will) help me out.

I have an Excel (2010) containing an overview of project budgets and invoices.
On a dashboard, I need to show the number of projects that are over budget. This array formula seems to do the trick; ==> {Sum(If(Invoice>Budget;1;0)}
But I also need to show the amount that is over budget ==> if Invoice > Budget then calculate difference and add to total. (if under budget do NOT add to total)

Constraints:
I prefer NOT to add an extra column (I-B), but would like to have the calculation on the fly in one single (array?) formula
I prefer to use namde references (using tables and table-columns) instead of A1 references.

The file attached gives overview of what I already have and what I need.

Hopefully one can help me out over here?

 

[Nebu]

Hi:

Try this :

=SUM(IF(A14:A21<B14:B21,Tabel1[Invoiced]-Tabel1[Budget]))
Array formula.

Thanks

 

[Faseeh]

Pleaes try:

=SUMPRODUCT(((A3:A10-B3:B10)<0)*(B3:B10-A3:A10))

 

[Joris Hermans]

Thanks to Nebu. Your solution seems to do the job as needed.

SOLLUTION: this array formula

{=SUM(IF(table1[Invoiced] > table1[Budget], Table1[Invoiced] - table1[Budget]))}

Thank you very much.