SUMIFS with OR conditions

[morbdetro]

I have a spreadsheet that looks at usage of part numbers that are interchangeable and combines them into a single usage total.
The formula I am currently using is:

=SUMIFS(Usage!$D:$D,Usage!$L:$L,$B$1,Usage!$B:$B,A140)*-1+SUMIFS(Usage!$D:$D,Usage!$L:$L,$B$1,Usage!$B:$B,B140)*1+SUMIFS(Usage!$D:$D,Usage!$L:$L,$B$1,Usage!$B:$B,C140)*-1

My goal is to try and reduce it to something like

=SUMIFS(Usage!$D:$D,Usage!$L:$L,$B$1,Usage!$B:$B,A140 OR B140 OR C140)*-1 (which I know doesn’t work.)

The only solution I have found is to enter the actual values into the formula instead of the cell reference.

=SUMIFS(Usage!$D:$D,Usage!$L:$L,$B$1,Usage!$B:$B,{"3469","114328","111484"})*-1

The problem is that I have 1576 lines and to enter in the values of all those cells is more work than I am willing to do, I am attempting to reduce the calculation time of the spreadsheet. Any help would be appreciated.

[Ron Coderre]

Try this:

Please Login or Register  to view this content.

Is that something you can work with?

[mlcb]

Maybe...

=-SUMPRODUCT(SUMIFS(Usage!$D:$D,Usage!$L:$L,$B$1,Usage!$B:$B,A140:C140))

[morbdetro]

Thank you both for the quick response. Both Formulas seem to be working as I need them to. I was wondering if either of you could explain why using the SUMPRODUCT produces what I am looking for?