In Excel, can you nest SUMIF fomulas?

For example sum in Column C where Column B="x" and Column A%26lt;today()





... and what would the syntax of the finished formula be?

In Excel, can you nest SUMIF fomulas?
You can, but it requires an old feature of Excel that is not very well known.





Enter this into the formula bar, but do not press enter. Instead, hold down Ctrl and Shift, THEN press enter. If you do it right, you should see the formula automatically surrounded by curly braces { }. If you do it wrong, it will likely give a VALUE error result.





=SUM(IF(A:A%26lt;TODAY(), IF(B:B="x", C:C, 0), 0))





What this does, is it replaces each column entry (A:A, B:B, C:C) with the cells in a row (A1, B1, C1), then evaluates the formula with every row. It's somewhat complex, but if you learn how to make them work you can do a lot.





Check out the link below for a short little tutorial.
Reply:You have two options when you want to sum based on more than one condition


1- Is adding new column with the IF function and your conditions all together wiht AND or OR functions, then SUM that column.





Add this function in column E


=IF( AND( B1="x", A1 %26lt; TODAY()), C1, 0)


then fill it down to the end of the table


Then do a regular SUM to column E





2- Use SUM and IF functions together and enter as array (CTRL + SHIFT + ENTER).





Enjoy my profile, I am the VBAXLMan
Reply:Use the sumproduct formula, it' not technically used for this but we are 'tricking' Excel into doing sumif's with more than 1 criteria:





= sumproduct((B2:B1000="x") *(A2:A1000%26lt;today()) *(C2:C1000))





A few pointers about this:


If you have a column header, do not include it in the formula. Notice that I began in row 2.


Each range must have the same number of rows.


Don't use the entire column. The formula creates an array in memory and that will slow down calculations.


You can have as many criteria as you like but one of them has to be the range you are summing. (I always put that one last)