Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

advanced sumifs with many criterias for each criteriaarea

Posted on 2012-09-18
14
Medium Priority
?
1,699 Views
Last Modified: 2012-09-18
Hi Experts,

I have a problem creating an advanced sumifs formula, that I hope you can help me with:

I have to select a number of accounts which should be my first criteria, from a number of projects, and sum the amounts that fullfills the combination of one of the accountnumbers and one of my projects.

Can that be done? I supposed that it can be done by putting in an OR statement as my criteria in both criterias, but it does not work for me.

best regards

Jørgen
0
Comment
Question by:Jorgen
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 400 total points
ID: 38409061
Hi,

try something like

=SUMPRODUCT(--ISNUMBER(MATCH(ProjRange,ProjCriteriaRange,0)),--ISNUMBER(MATCH(AccountRange,AccountCriteriaRange,0)),AmountRange)

Reaplce the texts with actual range.

Kris
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38409067
A sample workbook would help - do you have lists of the accounts and projects in a worksheet somewhere? SUMPRODUCT is probably going to be the simplest formula option.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38409109
If it's just those two criteria then you can use a formula like this

=SUM(SUMIFS(C:C,A:A,{"x","y","z"},B:B,{"a";"b"}))

That sums column C when column A is any of "x","y" or "z" and column B is any of "a" or "b". Note that the separators in {"x","y","z"} are commas and in {"a";"b"} are semi-colons.

regards, barry
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:Jorgen
ID: 38409168
Hi Barry,

As far as I can see that did not work for me. I tried to filter in the attached file, and gets the result 106,793.06 but using your formula gives me 87,473.21 using the formula below

=SUM(SUMIFS (B:B,A:A,{410202,410203,410204},C:C,{"Project 1","Project 2"}))

regards

Jørgen
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38409185
You missed the semicolons in the second array (as in Barry's version)
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38409227
Hi Rorya,

As we here in Denmark uses a semicolon instead of the commas, I expected that I should change the semicolon to a comma, but that did not work, do you also have a suggestion for which sign I should use?

regards

Jørgen
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38409233
you need commas in the first array and semicolons in the second


=SUM(SUMIFS (B:B;A:A;{410202,410203,410204};C:C;{"Project 1";"Project 2"}))

presumably
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38409238
Does not work,

Is it possible for you to return the spreadsheet as attachment, because it will convert if I open in my Excel version
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38409272
What spreadsheet?
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38409345
For some reason I can not upload the sheets from this machine

I will upload in a couple of hours
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38409389
I think for Danish regional settings it would be semi-colon and backslash like this

=SUM(SUMIFS(B:B;A:A;{410202;410203;410204};C:C;{"Project 1"\"Project 2"}))

regards, barry
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38409455
Hi Barry,

that worked well. Can you shortly explain the use of semicolons and  backslashes in this? You have deserved the points in any case, and I will reward you shortly if you do not have time.

regards

Jørgen
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 1600 total points
ID: 38409583
When you use a single multi-option criterion like this

=SUM(SUMIFS(B:B;A:A;{410202;410203;410204}))

the SUMIFS part creates an "array" of results like this

{10;0;13}

and SUM is used to sum that array

....but when you have 3 options for one criterion and 2 for another there are 2x3 possible combinations so you want the SUMIFS formula to return a 2X3 matrix and using semi-colons and backslashes mimics a column/row setup, e.g. if you put the first 3 numbers (410202, 410203 and 410204) in the cell range E2:E4 and "Project 1" and "Project 2" in cell range F1:G1 then you can get the same effect with this formula

=SUMPRODUCT(SUMIFS(B:B;A:A;E2:E4;C:C;F1:G1))

....but note that E2:E4 is a column and F1;G1 is a row, you must have one of each (although it doesn't matter which way round).

Also note that in that latter version I used SUMPRODUCT in place of SUM because that avoids the need for CTRL+SHIFT+ENTER, but SUMIFS is doing most of the work. For that reason this is a more efficient approach than using SUMPRODUCT as per Kris's suggestion..... BUT the SUMPRODUCT with ISNUMBER(MATCH approach is more flexible in that it would allow a 3rd (or 4th or 5th etc.) multi-option criterion (which SUMIFS doesn't) .....and you don't need to be so particular about the separators, MATCH can use either semi-colon or backslash in any combination (although not mixed within each individual MATCH function, obviously)

regards, barry
0
 
LVL 4

Author Closing Comment

by:Jorgen
ID: 38409603
Fantastic I learned a lot on array calculation as well. Thanks to all of you
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question