Sumifs OR

[bennymc10]

Is it possible to sumifs an argument but then sumifs a different argument if the criteria is not met with the first sumifs within the same cell?

I’m trying to get the value of a cell when the criteria in met however if the criteria is not met that will sumifs a different criteria.

Apologies if none of this makes sense!!

 

[PCL]

If the first SUMIF gives an error ( criteria not met) you can include another one in a IFERROR formula

 

[bennymc10]

I’ve tried that however it doesn’t seem to work. This is my current formula

=SUMIFS(Order[[#All],[Tonnes]],Order[[#All],[W+SZ+CON+CUST+PEP]],[@[W+SZ+CON+CUST+PEP]])+SUMIFS(Order[[#All],[Tonnes]],Order[[#All],[W+CUST+PEP]],[@[W+SZ+CUST+PEP]])

 

[joeu2004]

@bennymc10.... It would be easier to answer your question if you provided a concrete example of the two or more SUMIFS and/or criteria.

Generally, no, we cannot use SUMIFS to sum based on one criteria __or__ another.

Instead, we might us one of the following formulas, whichever makes more sense to you, all normally-entered (just press Enter as usual):

SUMPRODUCT(A1:A10,--((B1:B10=123) + (B1:B10=456) > 0))

SUM(SUMIFS(A1:A10,B1:B10,{123,456}))

SUMPRODUCT(A1:A10*(B1:B10={123,456}))

I prefer the first form, insofar as it is more generally applicable.

All say: sum A1:A10 where the corresponding value is B1:B10 is 123 or 456.

 

[joeu2004]

@bennymc10.... It would be easier to answer your question if you provided a concrete example of the two or more SUMIFS and/or criteria.

In the interim, you did. I would suggest the following:

=SUMPRODUCT(Order[[#All],[Tonnes]],
--((Order[[#All],[W+SZ+CON+CUST+PEP]] = [@[W+SZ+CON+CUST+PEP]])
 + (Order[[#All],[W+CUST+PEP]] = [@[W+SZ+CUST+PEP]]) > 0))

PS.... I wonder if [W+CUST+PEP] [sic] should be [W+SZ+CUST+PEP].

 

[joeu2004]

After-thought....

This is my current formula

=SUMIFS(Order[[#All],[Tonnes]],Order[[#All],[W+SZ+CON+CUST+PEP]],[@[W+SZ+CON+CUST+PEP]])+SUMIFS(Order[[#All],[Tonnes]],Order[[#All],[W+CUST+PEP]],[@[W+SZ+CUST+PEP]])

In what way does that formula not work?

Do you get a syntax error?

Or do you get an unexpected result?

If the latter, is it because your formula is double-accounting when both conditions are true in the same row?

The SUMPRODUCT formula that I suggested should correct for double-accounting.

And my PS might address a syntax error or unexpected result because: I wonder if [W+CUST+PEP] [sic] should be [W+SZ+CUST+PEP].

If neither solves your problem, you might need to explain the problem better (more specifically). And we might need to see a concrete example.

To "post" a concrete example, I think it is best if you upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website (e.g. box.net/files), and post the public/share URL in a response here. Test the download URL first, being careful to log out of the file-sharing website first. (If you use box.net/files, ignore any preview errors, and just download.)

Some people object to that because they cannot or will not download files. But posting tables of values and formulas might be tedious and insufficient. Sometimes, the devil is in the details.

 

[PCL]

@joeu2004
The use of SUMPRODUCt gives certainly a better understanding that SUMIFS
For the first one I would have use
=SUMPRODUCT(A1:A10;-- ( ( (B1:B10=123) + (B1:B10=456) )>0 ) )
No ?
@bennymc10
Yes please send us more detailed example

 

[joeu2004]

I would have use
=SUMPRODUCT(A1:A10;-- ( ( (B1:B10=123) + (B1:B10=456) )>0 ) )
No ?

Or how about:

=SUMPRODUCT(A1:A10;--( ( ( ( (B1:B10=123) + (B1:B10=456) ) ) ) > 0 ) )



The point is: they are equivalent. All of the colored parentheses are redundant.

I choose to write IF(A1+B1 > 0) because it is sufficient, given operator precedence.

Some people prefer to write IF( (A1+B1) > 0 ) either because they do not understand operator precedence or because they prefer to do things one way, given that parentheses are sometimes necessary to override operator precedence.

It is a matter of personal taste.

 

[Aladin Akyurek]

I’ve tried that however it doesn’t seem to work. This is my current formula

=SUMIFS(Order[[#All],[Tonnes]],Order[[#All],[W+SZ+CON+CUST+PEP]],[@[W+SZ+CON+CUST+PEP]])+SUMIFS(Order[[#All],[Tonnes]],Order[[#All],[W+CUST+PEP]],[@[W+SZ+CUST+PEP]])

Care to post a part (the upper part) of your table?