• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using OR function witihn SUMIFS function

Add 2 SUMIFS together. E.g, "Sum of all numbers greater than 5, or less than 2"

=SUMIF(A:A,">5")+SUMIF(A:A,"<2")
 
I like Luke's more direct method.


I have recently discovered the array of using sumproduct and I have been trying to find out its capacity. so my take is a bit cumbersome.


{ = SUMPRODUCT( IF( number range is less than 2,1,0),IF(number range is less than 2 , number range,0)) + SUMPRODUCT (IF( number range is greater than 5,1,0) , IF(number range is greater than 5,number range, 0 ) )}
 
Thanks Fred.

You can do away with the array, and the IF functions in your SUMPRODUCT by putting a double "-" in front of each true/false check (converts the text to numbers). For an OR statement, you add them.

Regaular example for multi-criteria:

=SUMPRODUCT(--(Range1>2),--(Range2="Bob"),NumberRange)

OR type critera:

=SUMPRODUCT((Range1<2)+(Range1>5),NumberRange)
 
Luke


I tried your suggestion of adding multiple SUMIFS. But I did not get an answer


My requirement is as follows


=SUMIFS(total column,criteria1 col,criteria 1, criteria 2 col, criteria 2, criteria 3 col, criteria 3, criteria 4, OR(not equal to any 1 of 3 possible values to be checked))
 
By saying not equal to any 1 of 3 possible values you mean equal to either of the other two possibilities.


so wouldn't that be:

=SUMIFS(Total ,Col 1, Crit 1, Col 2, Crit 2, Col 3, Crit 3, Col4, Crit 4) + Sumifs(Total, Col 5, Crit 5.1) + Sumifs(Total, Col 5, Crit 5.2) ?


Otherwise can you post your data and highlight what you require?

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
For Criteria 4, I have to ignore all the 3 values.

For e.g. if the values for that column are A, B, C, D, E, F, G. I need to ignore the values E & F & G


Just to add I am able to get the desired results if use an ms-access query but I would like to use Excel formula
 
Can you post your data and highlight what you require?

Only a part of the data is necessary

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
You must log in or register to reply here.
Back
Top