Using SUMIFS with multiple AND OR conditions

Question

I would like to create a succinct Excel formula that SUMS a column based on a set of AND conditions, plus a set of OR conditions.

My Excel table contains the following data and I used defined names for the columns.

• Quote_Value (Worksheet!$A:$A) holds an accounting value.
• Days_To_Close (Worksheet!$B:$B) contains a formula that results in a number.
• Salesman (Worksheet!$C:$C) contains text and is a name.
• Quote_Month (Worksheet!$D:$D) contains a formula (=TEXT(Worksheet!$E:$E,"mmm-yy"))to convert a date/time number from another column into a text based month reference.

I want to SUM Quote_Value if Salesman equals JBloggs and Days_To_Close is equal to or less than 90 and Quote_Month is equal to one of the following (Oct-13, Nov-13, or Dec-13).

At the moment, I've got this to work but it includes a lot of repetition, which I don't think I need.

=SUM(SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Oct-13")+SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Nov-13")+SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Dec-13"))

What I'd like to do is something more like the following but I can't work out the correct syntax:

=SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,OR(Quote_Month="Oct-13",Quote_Month="Nov-13",Quote_Month="Dec-13"))

That formula doesn't error, it just returns a 0 value. Yet if I manually examine the data, that's not correct. I even tried using TRIM(Quote_Month) to make sure that spaces hadn't crept into the data but the fact that my extended SUM formula works indicates that the data is OK and that it's a syntax issue. Can anybody steer me in the right direction?

Answer 1

With the following, it is easy to link the Cell address...

=SUM(SUMIFS(FAGLL03!$I$4:$I$1048576,FAGLL03!$A$4:$A$1048576,">="&INDIRECT("A"&ROW()),FAGLL03!$A$4:$A$1048576,"<="&INDIRECT("B"&ROW()),FAGLL03!$Q$4:$Q$1048576,E$2))

Can use address / substitute / Column functions as required to use Cell addresses in full DYNAMIC.