• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

Sumifs

What is wrong with my formula?
Sumifs-Formula.xlsx
0
Pete Edwards
Asked:
Pete Edwards
1 Solution
 
Pete EdwardsAuthor Commented:
BTW the formula is cell "R6" in the selling expense tab.

Thanks
0
 
Saqib Husain, SyedEngineerCommented:
Try

=SUMIF(Sheet2!$A:$A,"11020",Sheet2!$F:$F)+SUMIF(Sheet2!$A:$A,"11030",Sheet2!$F:$F)
0
 
Pete EdwardsAuthor 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Open in new window

Regards
0
 
Saqib Husain, SyedEngineerCommented:
Or the ARRAY formula

=SUM(SUMIFS(Sheet2!$F:$F,Sheet2!$A:$A,{"11020","11030"}))
0
 
Saqib Husain, SyedEngineerCommented:
or

=SUMPRODUCT(SUMIFS(Sheet2!$F:$F,Sheet2!$A:$A,{"11020","11030"}))
0
 
Saqib Husain, SyedEngineerCommented:
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
 
als315Commented:
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
 
Pete EdwardsAuthor 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
 
stevepcguyCommented:
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
 
Pete EdwardsAuthor 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
 
Saqib Husain, SyedEngineerCommented:
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
 
byundtCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
barry houdiniCommented:
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
 
Pete EdwardsAuthor Commented:
Thanks everybody.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now