Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Sumifs

Posted on 2013-11-05
Medium Priority
333 Views
What is wrong with my formula?
Sumifs-Formula.xlsx
0
Question by:Pete Edwards

Author Comment

ID: 39624884
BTW the formula is cell "R6" in the selling expense tab.

Thanks
0

LVL 43

Expert Comment

ID: 39624911
Try

=SUMIF(Sheet2!\$A:\$A,"11020",Sheet2!\$F:\$F)+SUMIF(Sheet2!\$A:\$A,"11030",Sheet2!\$F:\$F)
0

Author Comment

ID: 39624921
I appreciate the advice, but I'm trying to shake off the dust on a "sumifs" formula and I wrote this as practice to see if I still knew how to perform this formula.  I'm just perplexed on why this won't tally the two accounts I've asked it to calculate.

Could you advise in this regard.

Thanks
0

LVL 53

Expert Comment

ID: 39624925
Hi,

you cannot have the same criteria range twice (it does not makes sense)

pls try

``````=SUMIF(Sheet2!\$F:\$F,Sheet2!\$A:\$A,"11020")+SUMIF(Sheet2!\$F:\$F,Sheet2!\$A:\$A,"11030")
``````
Regards
0

LVL 43

Expert Comment

ID: 39624929
Or the ARRAY formula

=SUM(SUMIFS(Sheet2!\$F:\$F,Sheet2!\$A:\$A,{"11020","11030"}))
0

LVL 43

Expert Comment

ID: 39624934
or

=SUMPRODUCT(SUMIFS(Sheet2!\$F:\$F,Sheet2!\$A:\$A,{"11020","11030"}))
0

LVL 43

Expert Comment

ID: 39624947
The reason that your formula would not work your way is that this formula gives values when BOTH conditions are true. Not when EITHER condition is met.
0

LVL 40

Expert Comment

ID: 39624950
Sumif criterias means:
Sum if A ="11020" AND A = "11030"
in your case it is impossible, so you get 0. It is good if you like to sum some range:
A < 10 AND A > 5, for example
0

Author Comment

ID: 39624951
Okay.  Was not aware of that aspect.  I can use the array you've provided, but if its not to much trouble could you use the file I've provided and populate a "correct application" of the sumifs, I'm targeting on utilizing this formula and just want to make sure I understand it fully.  If you write it out, I believe I will be able to follow the logic.

Thanks
0

LVL 8

Expert Comment

ID: 39624966
You have multiple criteria that exclude one another. You're asking Excel to add column f if column A is 11020, AND if column A is 11030. No single row can have column A equal both those values.

You can break the formula down and it should work:
=SUMIFS(Sheet2!\$F:\$F,Sheet2!\$A:\$A,11020)+SUMIFS(Sheet2!\$F:\$F,Sheet2!\$A:\$A,11030)
0

Author Comment

ID: 39624988
Okay, think it's starting to click.  So, if I want to utilize a "sumifs" where I ask several criteria within "one" sumifs I need to have several "collumns" where "multiple" rows match to bring back one value, correct.

Can anybody manipulate my spreadsheet to provide an example please of a "single" sumifs with multiple request to bring back a value.

Thanks
0

LVL 43

Expert Comment

ID: 39625007
Try the above provided formulas in your spreadsheet. You just have to paste the formula there. For the ARRAY formula you will have to press shift-ctrl-enter to get the desired result.
0

LVL 81

Expert Comment

ID: 39625098
Contrary to his statement, ssaqibh's "ARRAY" formula doesn't need to be array-entered to give the correct answer:
=SUM(SUMIFS(Sheet2!\$F:\$F,Sheet2!\$A:\$A,{"11020","11030"}))

You could also write it as a SUMIF formula (doesn't need to be array-entered either):
=SUM(SUMIF(Sheet2!\$A:\$A,{"11020","11030"},Sheet2!\$F:\$F))
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 1600 total points
ID: 39625204
Brad, that is strange. I tried normal but got only the first criteria. Then I tried ARRAY and it worked. Now it works normal too.
0

LVL 50

Expert Comment

ID: 39625247
In general if you use an "array constant" like {"11020","11030"} then CTRL+SHIFT+ENTER isn't required - if you replace that with some cell references like G2:G3 then you do need CSE....or SUMPRODUCT in place of SUM to avoid "array entry".

Also in SUMIF/SUMIFS there's no distinction made between text and number so you don't really need quotes around those numbers (whether the data is text or numeric) so this would be sufficient

=SUM(SUMIF(Sheet2!\$A:\$A,{11020,11030},Sheet2!\$F:\$F))

......or if you want short this slightly shorter version gives the same result

=SUM(IF(Sheet2!\$A:\$A={11020,11030},Sheet2!\$F:\$F))

....array entered.......but probably not recommended as the SUMIF will be faster

regards, barry
0

Author Closing Comment

ID: 39625471
Thanks everybody.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing howâ€¦
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calculâ€¦
###### Suggested Courses
Course of the Month6 days, 12 hours left to enroll