Sumif based on a text criteria and a date range

Hi all,

i am new to this thread so i apologize if i miss something or this has been posted before (i looked everywhere i could but i can't find anything even close to what i am trying to do)

i am trying to build a series of reports off a set of source data.

The source data is in one sheet in the following structure:

Column1 - Date
Column2 - Warehouse Code
Column3-36 - Different Sales Metrics

I need to build a formula that can sum together the results for one of the sales metrics for a warehouse code in a set period. Eg, i want to know the number of phones (sales metric) sold by warehouse abc123 between 01/05/2010 and 20/05/2010 (i need to be able to copy this formula down through a large number of cells).

I need to be able to have as many days and as many different warehouses in the source data as i want

I have tried to do something with a combined date and warehouse code column, but this doesn't get me anywhere because i cannot define a date range when the data is in this format.

I have also tried using a array based sum if formula that looks at multilple criteria, and while this technically works, with the size of the reports i am building, it would take my sheet about 10 minutes to calculate


i hope what i have described makes sense, and i hope some can help

thanks in advance

What you're trying to do is a sumif with multiple criteria ("ifs"). Sumif can only handle one criterion. SUMIFS, on the other hand, can handle multiple criteria.

It sounds like you want to use this formula to sum up different ranges based on your date/code. Lets say you're taking the sum of metrics C4:C12, your warehouse codes are in B4:B12, and your dates are in A4:A12. Then enter your range in cells A1, A2, and A3:
put the beginning date in the range in cell A1; the ending date in the range in cell A2; and the warehouse code you want to use in the formula in cell A3. Then you can use a formula like this: =SUMIFS(C4:C12,B4:B12,A3,A4:A12,">"&A1,A4:A12,"<"&A2)

By using references (A1, A2, A3) rather than actual expressions (abc123, 1/1/10, etc.) you can use this one formula with any inputs. Note though that using it like this will exclude the front and back end of the date range. So if you want to sum between 15/3/2010 and 30/3/2010 including those dates, you would need to enter 14/3/2010 and 31/3/2010 as your dates. If you want to move the range inputs to a different area of the sheet, be sure to use CUT (ctrl x) rather than copy, so that the references will still work after you paste.

If you provide a little more detail on your project I might be able to suggest something more suitable to your situation. You said you want to copy the formula down? Please give a little more detail on how exactly you need to use this.