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

Why SumIf not working with this condition?

Dheeraj

Member
Hi All,


I have a situation in which based on a condition I need to get total from multiple columns. Below showing sample data:


Column-A Column-B Column-C Column-D

Person1 5000 10000 15000

Person2 5000 5000 10000

Person2 4500 20000 15000

Person3 12000 15000 10000

Person1 1000 8000 7000


If I apply formula =sumif(A2:A6,"Person1",B2:D6) it is not giving correct output i.e it is totaling values in column-B only. However if I apply an array formula =sum(if(A2:A6,"Person1",B2:D6)) I get the correct total.


My problem is that I am unable to understand why simple SumIf is not working.


Thanks,

Dheeraj
 
Try


SUMPRODUCT((A2:A6="P2")*B2:D6)


Read more here:

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
 
Hi, Dheeraj!


SUMIF function can handle different range sizes for first and third arguments but with certain restrictions: as stated in the function help embedded within Excel (select cell with SUMIF formula, press "f(x)" at left of edit bar and then F1 for Help about this function) when sum_range differ in size and type from criteria_range (1st argument), Excel uses the top left cell of sum_range as initial cell and includes cells that correspond with size and type of criteria_range (at least in the spanish Excel 2010 version the provided data for many cases is wrong and doesn't match with the example table given there).

But in my opinion it might be deduced that the prevailing range is criteria_range and not sum_range, that's why in your formula the second and third data columns aren't added because their inconsistency with the the criteria "Person1".


Regards!
 
Back
Top