4

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?

Click hear for image

4

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))))

enter image description here

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.

-1

You can use this Screen shot to apply the Formula, will get the Sum of Blue between the given Dates.

enter image description here

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

NB:

A300:A306 has Colour's Name.

B299:I299 has Date Values.

C309 had Colour name BLUE.

C310 & C311 has Start and End Date Value.

B300:I306 has Numeric Values to match with the Colour and SUM.

Alter the Data Range according to your Worksheet's illustration.

Hope this help you.

  • I assume you test your answers on a worksheet, and drop down a few rows for a new question. Which is why your answer refers to cells in rows >= 300, when the OP's example is all in rows 1 - 15. This makes your answer very hard to follow. Readers glance at it briefly, then move on to any other answer. It would be easy for you to open a new sheet for each new answer. Look at pat2015's answer and compare it to yours. His is clear, succinct, includes a picture of the results, and doesn't use bold type. His answer is excellent, yours is a waste of everyone's time. – Bandersnatch Jan 24 '18 at 16:29
  • @Bandersnatch Yes, I've tested the same Data OP has posted but in different Data Range, I think it doesn't matters, what matters is whether the Formula is working properly or not. Yes it's working and getting the Answer 92. – Rajesh S Jan 25 '18 at 6:33
  • @Bandersnatch, for better understanding now I've uploaded the screen shot. The First 92 is generated by Pat's Formula and the second 92 is of mine Formula. And what I do believe that, the OP/ Readers can alter the Data Range without any hassle !! – Rajesh S Jan 25 '18 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.