try something like

=SUMPRODUCT(--ISNUMBER(MAT

Reaplce the texts with actual range.

Kris

Solved

Posted on 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

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

14 Comments

try something like

=SUMPRODUCT(--ISNUMBER(MAT

Reaplce the texts with actual range.

Kris

=SUM(SUMIFS(C:C,A:A,{"x","

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

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,41

regards

Jørgen

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

=SUM(SUMIFS (B:B;A:A;{410202,410203,41

presumably

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

=SUM(SUMIFS(B:B;A:A;{41020

regards, 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

=SUM(SUMIFS(B:B;A:A;{41020

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

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

regards, barry

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

Connect with top rated Experts

**21** Experts available now in Live!