Sumifs

What is wrong with my formula?
Sumifs-Formula.xlsx
Who is Participating?

EngineerCommented:
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

Author Commented:
BTW the formula is cell "R6" in the selling expense tab.

Thanks
0

EngineerCommented:
Try

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

Author Commented:
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

Commented:
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

EngineerCommented:
Or the ARRAY formula

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

EngineerCommented:
or

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

EngineerCommented:
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

Commented:
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 Commented:
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

Commented:
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 Commented:
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

EngineerCommented:
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

Commented:
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

Commented:
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 Commented:
Thanks everybody.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.