# I am sorry if this is a dumb question

#### Joelb58

##### New Member
But any help would be appreciated.

I have a range of data with irregular dates listed as column headers. For instance

12/27 12/20 12/13 11/29
Apples 1 5 10 32
Bananas 3 7 12 15
Oranges 6 9 32 34

Is there a formula that will allow me to only look at data over a certain date range and by type of fruit?

For instance, say I wanted to compute an average of the number of bananas from 11/29 to 12/20 but it would sitll give me the ability to change my intended dataset to Oranges over 12/20-12/27 if I later wanted that average.

Hope this is clear

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Use a Pivot Table

Use a Pivot Table

Sorry I do not think this is what I want, I want to be able to toggle the date range and quickly get an average returned

I'd recommend putting the year on the dates for clarity but with the criteria in F, G, and H you could try something like

=AVERAGE(IF((B1:E1>=G1)*(B1:E1<=H1)*(A2:A4=I1),B2:E4)) control shift enter

 A B C D E F G H 12/27/2014 12/20/2014 12/13/2014 11/29/2014 12/1/2014 12/30/2014 apples Apples 1 5 10 32 5.33333333 Bananas 3 7 12 15 Oranges 6 9 32 34

<TBODY>
</TBODY>

I'd recommend putting the year on the dates for clarity but with the criteria in F, G, and H you could try something like

=AVERAGE(IF((B1:E1>=G1)*(B1:E1<=H1)*(A2:A4=I1),B2:E4)) control shift enter

 A B C D E F G H 12/27/2014 12/20/2014 12/13/2014 11/29/2014 12/1/2014 12/30/2014 apples Apples 1 5 10 32 5.33333333 Bananas 3 7 12 15 Oranges 6 9 32 34

<tbody>
</tbody>

What if I wanted the sum of the rows of fruit for different date ranges

So if I wanted...

Start date (enter 12/13)
End Date (enter 12/27)

Apples "Sum of numbers that fall in the row specified by date range above"
Bananas "Sum of numbers that fall in the row specified by date range above"
Oranges "Sum of numbers that fall in the row specified by date range above"

essentially the formula would be the same just replacing Average with Sum.

as far as 'not working', do you mean an error or the wrong result?

as far as the date range, is it inclusive of the dates entered?

are you sure the year in the column headers are the same as the criteria?

=SUM(IF((B1:E1>=G1)*(B1:E1<=H1)*(A2:A4=I1),B2:E4))

and remember its an array so you need to enter it with Control Shift Enter

 12/27/2014 12/20/2014 12/13/2014 11/29/2014 12/13/2014 12/27/2014 oranges Apples 1 5 10 32 47 Bananas 3 7 12 15 Oranges 6 9 32 34

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=3><COL><COL span=2><COL></COLGROUP>

I suppose technically if we're talking about the SUM I would do it a little different and probably use something like

=SUMPRODUCT((B1:E1>=G1)*(B1:E1<=H1)*(A2:A4=I1),B2:E4)

which would be able of handling the arrays and not require control shift enter

so I guess if you wanted to do the average this way you could use something like...

=SUMPRODUCT((B1:E1>=G1)*(B1:E1<=H1)*(A2:A4=I1),B2:E4)/SUMPRODUCT((B1:E1>=G1)*(B1:E1<=H1)*(A2:A4=I1))

Last edited:

Replies
2
Views
149
Replies
4
Views
148
Replies
3
Views
868
Replies
4
Views
119
Replies
5
Views
131

1,196,408
Messages
6,015,102
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back