# Sum of cells based on a vertical criteria and horizontal date range

Hoping someone can help me!

I am looking for a formula that will sum a range based on a vertical criteria and a horizontal criteria - but this second one is actually a date range not a single date.

I have dates in row 1 and variables in column A (see linked image below).

I am looking for a formula that will sum multiple cells based on a start and finish date for a certain 'variable'.

For example - In the linked image I have 3 fields that are input (the yellow cells C11, C12 & C13). Using these 3 fields I want it to find the cells they are referring too (the blue cells) and give the sum of these in the "total" (green cell F11)

Can anyone help with a formula? Try this solution using SUM & IF in an Array Formula. Sample data is in cells A2:I9. Date format is mm/dd/yyyy in my Excel. C12, C13 & C14 hold Type, From and To dates respectively. Total is displayed in F12.

In F12 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an array formula. The formula shall now be enclosed in curly braces to indicate that it's an array formula. This step is important, the formula shall only work correctly if it's an Array Formula.

``````=SUM(IF(\$A\$3:\$A\$9=C12,IF(\$B\$2:\$I\$2<=C14,IF(\$B\$2:\$I\$2>=C13,\$B\$3:\$I\$9,0))))
`````` For the sumproduct formula, I found that separating the criteria array from the data reference array (with a comma) works better in the event you have multiple columns or rows that meet the criteria. The format of the function is SUMPRODUCT(array1, [array2], [array3], ...) and apparently it doesn't require more than a single array, but again I found it works better to show as two arrays. So in Ragesh's example it would like this:

=SUMPRODUCT((A300:A306=C309)(B299:I299>=C310)(B299:I299<=C311),B300:I306)

with the only difference being the comma before B300. In my example, my dates across the top were in weeks, and I wanted to consolidate data into months, which worked only if i used the comma. Personally, I prefer the format of the sumproduct to the Sum(If) or SUMIFS versions simply because I can remember it more easily, but it's personal preference.

You can use this Screen shot to apply the Formula, will get the Sum of Blue between the given Dates. ``````=SUMPRODUCT((A300:A306=C309)*(B299:I299>=C310)*(B299:I299<=C311)*B300:I306)
``````

NB:

A300:A306 has Colour's Name.

B299:I299 has Date Values.