MS EXCEL SUMIFS formula

[mazahrm]

Dear All,


Can i kindly request for someone to advise me why the formula below is not working,

i am at the first column and the second where value = 1 sum the third column


Product - MS Excel 2010


1 45 454

2 45 154

2 14 124

2 25 154

2 63 55

3 63 144

4 1 145

4 12 4


0 =SUMIFS(H7:H14,G7:G14,1,F7:F14,1)


Thank You - all help appriciated

 

[vijaySharma]

Mazahrm,


Try the below to get the results....


=SUM(IF(F7:F14=1,H7:H14),IF(G7:G14=1,H7:H14))


This is a array formula, press CTRL + SHIFT + Enter to get the output.


In the meantime trying to figure out why SUMIF and Sumproduct is also not giving the results.... looks like a case of AND condition coming into effect.


~VijaySharma


*EDIT*


It is the position of the elements due to which SUMIFS and SUMPRODUCT are failing.

 

[vijaySharma]

Mazahrm,


Read the below to understand why SUMIFS was not giving the result (copied from online help).


Each cell in the sum_range argument is summed only if all of the corresponding criteria specified are true for that cell. For example, suppose that a formula contains two criteria_range arguments. If the first cell of criteria_range1 meets criteria1, and the first cell of criteria_range2 meets critera2, the first cell of sum_range is added to the sum, and so on, for the remaining cells in the specified ranges.


~VijaySharma

 

[mazahrm]

Thank you for the response's very much appriciated,

but in the sample data provided in column A i have the value 1 and in column B i have value 1 as well in the formula it specifies that where criteria = 1 in criteria range 1and criteria range 2 therefore i sould get a value of 454+145

but the result is 0

am i still missing the point ?

 

[vijaySharma]

It is the position of the elements of the array which will be summed up...


you can try using SUM (SUMIF(range, criteria sum_range) + SUMIF(range, criteria sum_range))


In your sample data, if you position 1 at the same cells in Col A and B you will get the answer. So if Cell A2 and B2 have 1, you will get the answer.

HTH

 

[mazahrm]

Thank you for your help, now understood the concpet