I am sorry if this is a dumb question

Joelb58

New Member
Joined
Jul 17, 2010
Messages
8
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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</SPAN>
12/20/2014</SPAN>
12/13/2014</SPAN>
11/29/2014</SPAN>
12/1/2014
12/30/2014</SPAN>
apples</SPAN>
Apples</SPAN>
1</SPAN>
5</SPAN>
10</SPAN>
32</SPAN>
5.33333333
Bananas</SPAN>
3</SPAN>
7</SPAN>
12</SPAN>
15</SPAN>
Oranges</SPAN>
6</SPAN>
9</SPAN>
32</SPAN>
34</SPAN>

<TBODY>
</TBODY>
 
Upvote 0
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

ABCDEFGH
12/27/201412/20/201412/13/201411/29/201412/1/201412/30/2014apples
Apples1510325.33333333
Bananas371215
Oranges693234

<tbody>
</tbody>

Sorry none of this is working. Maybe I am not being clear. How about this..

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"
 
Upvote 0
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</SPAN>12/20/2014</SPAN>12/13/2014</SPAN>11/29/2014</SPAN> 12/13/2014</SPAN>12/27/2014</SPAN>oranges</SPAN>
Apples</SPAN>1</SPAN>5</SPAN>10</SPAN>32</SPAN> 47</SPAN>
Bananas</SPAN>3</SPAN>7</SPAN>12</SPAN>15</SPAN>
Oranges</SPAN>6</SPAN>9</SPAN>32</SPAN>34</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=3><COL><COL span=2><COL></COLGROUP>
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top