Sumifs

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

Thanks

Try

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

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

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")

Select allOpen in new window

Regards

Or the ARRAY formula

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

or

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

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.

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

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

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)

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

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.

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))

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

Thanks everybody.