• 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.

Sumifs

Hi Jayadev,


I have tried in many ways by putting AND/OR in SUMIF() but could not find result.


I think AND/OR can not come in SUMIF() however will wait for our Excel Gurus to clear our doubts on this situation.


Thanks

Anupam Tiwari
 
Hi jayadev,


When you are using SUMIFS() you automatically insert and AND/OR function. Here is the explanation:


The syntex of SUMIFS is:


Code: 
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)


By defining multiple criteria for a range lets say for A1:A10 & B1:B10, They All need to be "X" & "Y" respectively, you have already added an AND() condition. If you apply the same to only one Range Lets Say A1:A10 needed to be "X" or "Y" or "X" & "Y", you can see it fulfills both AND() and OR() requirement. To the second part of your question, yes you can use a named range in SUMIFS().


Your question can be answered in details if you be more specific.


Thanks

Faseeh
 
ok, for example - A1:A3 contains x,y and Z and B1:B3 contains 100,200 and 400. I want values for X and Y using sumifs. so My answer would be 300.


Kindly advise


Thanks

Jay
 
Hi Jayadev,


Use the below formula,

Code: 
=SUM(IF(($A$1:$A$3="x")+($A$1:$A$3="y"),$B$1:$B$3))


Confirm the formula by pressing Ctrl + Shift + Enter, not just Enter..


or 


=SUMPRODUCT(($A$1:$A$3="x")+($A$1:$A$3="y"),$B$1:$B$3)

without CSE..


Regards,

Deb
 
Hi Jayadev Sarangi


you want to use the SUMIFS FORMULA


and your data is stored in the Column A and Column B


Place the X in the Column C in row1 and place Y in the Column C of Row 2


use the formula in C1


=SUMFS(A$1:A$3,B$1:B$3,C1)


DRAG DOWN THE FORUMLA


STILL YOU ARE FACING PROBLEM THEN IT IS BETTER TO UPLOAD A SAMPLE FILE


WITH REGARDS


SP
 
You must log in or register to reply here.
Back
Top