You are here:

Advertisement

I have this formula that works, but is looking for a way to simplify the formula.

Example

=SUMIFS(Sheet2!O:O;Sheet2!B:B;I23;Sheet!F:F;J23;Sheet2!G:G;K23)+ =SUMIFS(Sheet2!O:O;Sheet2!B:B;I23;Sheet!F:F;J23;Sheet2!G:G;K24)

I have tried this but it just returns 0

=SUMIFS(Sheet2!O:O;Sheet2!B:B;I23;Sheet!F:F;J23;Sheet2!G:G;K23:K24)

Can you help

Svein:

You cannot use an array, K23:K24, as a search value within a SUMIF (or SUMIFS) function. You will instead need to use a function designed to accept array input - I suggest SUMPRODUCT.

Here is the function you would use in Microsoft Excel:

=SUMPRODUCT((Sheet2!O:O)*(Sheet2!B:B=I23)*(Sheet2!F:F=J23)*((Sheet2!G:G=K23)+(Sheet2!G:G=K24)))

I see you are not using Microsoft Excel and I speculate you are using Adobe Spreadsheet Open Office (I know this because of your use of ";" instead of "," in the function). I don't know if this function will work the same in that program, but at the very least, you may need to convert it to the Adobe format.

Please note the function will fail if you have any text in column O (such as a column header). You can correct for that with the following function:

=SUMPRODUCT(IF(ISNUMBER(Sheet2!O:O),Sheet2!O:O,0)*(Sheet2!B:B=I23)*(Sheet2!F:F=J23)*((Sheet2!G:G=K23)+(Sheet2!G:G=K24)))

The above is a true array function and must be confirmed with CTRL-SHIFT-ENTER instead of just ENTER (it will be surrounded by braces {}).

Note - Neither of the two functions I provided are any more "simple" than the one you are currently using. I don't see anything wrong with having two SUMIFs tagged together in your original solution.

- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thanks for the answers, this was useful . I use Excel, but the Norwegian version only works with " ; " Sincerely Svein |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (

I have been using spreadsheets since Lotus 1-2-3 was released. **Education/Credentials**

Certified Public Accountant (CPA)