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

S

suell

Member
Hi, I have a column of salaries with the data starting in A2 to A200

I need to sum the salaries less than $300 and greater than $500.

I can do a SUM in an Array and get the correct answer but I want to do a SUMIFS statement, if possible? I have tried the following but I get zero as the result:

=SUMIFS(A2:A200,A2:A200,”<300”,A2:A200,”>500”)...what am I doing wrong?
 
Thanks for the SUMPRODUCT but I just wanted to know can I do an SUMIFS statement? Is this possible?
 
@Suell


Hi


as per my understanding you want to sum the amount of $300 & $ 500 only then try the below formula


=SUMIFS(A2:A14,A2:A14,">="&300,A2:A14,"<="&500)


OR


=SUMIFS(A2:A14,A2:A14,">="&D2,A2:A14,"<="&D3)


note the range of D2 and D3 is the specified Amount for exm. D2 has 300 and D3 has 500


then it will sum the values only 300 & 500


if you want only sum the particular amount then please try this


=SUMIFS(A2:A14,A2:A14,"300")


Hope it will clear your problem other wise please inform


Thanks


SP
 
I only have Excel 2003 so can't really check.


I imagine you will have to separate it as I read your formula to be sum all values which are less than 300 and greater than 500 (which is impossible). I could be wrong though, as I can not use SUMIFS with 2003.
 
Hi, I tried both your SUMIFS formulas and still get zero for both. When I do the single SUMIF for 300, I get the right answer..it just doesn't work out when you combine the 300 and 500.
 
@Suel


can you post the formula as you entered in your file


because both formulas is worked in my file


Thanks


SP
 
Hi, the following is the formula used and I have included ten amounts in A2 to A11, you should get $9800 as the correct answer....

=SUMIFS(A2:A11,A2:A11,"<"&300,A2:A11,">"&500)

$500,

$1000,

$200,

$600,

$1200,

$100,

$5000,

$1700,

$400,

$500
 
@Suell


your formula is wrong please change to


Your Formula


=SUMIFS(A2:A11,A2:A11,"<"&300,A2:A11,">"&500)


Changed Formula


=SUMIFS(A2:A11,A2:A11,">="&300,A2:A11,"<="&500)


if it is not correct then please upload a sample file with manual result


Thanks


SP
 
Hi Suell ,


Please understand how the SUMIFS statement works :


The criteria are ANDed together ; so when you use two criteria such as "<300" and ">500" , what you are saying is sum those amounts which are less than 300 AND greater than 500 ; it is clear that there cannot be an amount like this , which is why the result is 0.


What you have to do is add the results of two SUMIF statements ; the first SUMIF will add all those amounts which are less than 300 , and the second SUMIF statement will add all those amounts which are greater than 500. Adding these two results will give you your final answer.


Narayan
 
Hi NARAYANK991. I have done the two separate SUMIF statements with a plus sign and got the correct answer. I am used to using SUMIF and I thought could this be done using one SUMIFS statement but I see from what you are saying that it cannot be done. So I will stick with using SUMIF. Thanks
 
Hi suell,

you could also combine the conditions into a single SUMIFS formula as follows:

=SUM(SUMIFS(A2:A11,A2:A11,{"<300";">500"}))

enter with Ctrl + Shift + Enter


The SUMIFS returns an array with the first value equal to sum of all values less than 300, and second value equal to sum of all values greater than 500.

With your sample data, it returns {300, 9500}. SUM of those values gets you 9800.


Cheers,

Sajan.


P.S.

You could also use SUMIF as follows:

=SUM(SUMIF(A2:A11,{"<300";">500"}, A2:A11))

enter with Ctrl + Shift + Enter
 
Suell


The correct format for Sumifs is:

=SUMIFS(A2:A200,A2:A200,"<"&300,A2:A200,">"&500)

Except that this won't work as the logic is incorrect

The format of Sumifs is that it only sums when all conditions are met

and a Value cannot be below 300 and above 500, thats impossible
 
Hi Suell,

To simplify the formula further, you could use:

=SUM(SUMIF(A2:A11,{"<300";">500"}))

enter with Ctrl + Shift + Enter.


Cheers,

Sajan.
 
Hi suell!


Just another way.. :)


Code: 
=SUM($A$2:$A$11)-SUMIFS($A$2:$A$11,$A$2:$A$11,"<="&500,$A$2:$A$11,">="&300)


Regards,

Deb
 
Hi Dave,

You could always use

=SUMPRODUCT(SUMIF(A2:A11,{"<300";">500"}))


Cheers,

Sajan.
 
Hi Sajan,


Just FYI, because you're using an array constant you can just use SUM (no need for CSE).

[pre]
Code: 
=SUM(SUMIF(A2:A11,{"<300";">500"}))
[/pre]
Hi Dave,


The SUMPRODUCT() formula you posted will return the wrong result because it requires the cells in column A to be both less than 300 and more than 500 which is impossible. You'd have to change the formula to use an OR logic, for example by using +.
 
Hi, Thank you everyone for your input, greatly appreciated. So many variations. I learned a great deal.
 
You must log in or register to reply here.
Back
Top