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
Medium Priority
1,699 Views
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
Question by:Jorgen
• 6
• 4
• 3
• +1

LVL 18

Assisted Solution

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

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

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

LVL 4

Author Comment

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

ID: 38409185
You missed the semicolons in the second array (as in Barry's version)
0

LVL 4

Author Comment

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

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

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

ID: 38409272
0

LVL 4

Author Comment

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

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

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

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

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

Featured Post

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.
Suggested Courses
Course of the Month15 days, 20 hours left to enroll