# Find days in column, sum the value up and issue the average

#### BascherPA

##### New Member
Hello all,

I have a list in a spreadsheet called "SUB" with the column date and value.

In another spreadsheet "MAIN" I want to sum up all the date from the other spread sheets.

In this spreadsheet "MAIN" I have a cell where I want to search in "SUB" the date e.g. Monday and choose the value for this date and then search the next Monday and also choose this value and sum them up.

The scope should be the last line - 200 days.
I have a cell A2 in spreadsheet "SUB" with the number of the last line. Another cell with the value of A2 - 200.

Could somebody please tell me how to find the days in this scoop, sum up the values and issue the average of this sum.

Best regards
Benjamin

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Eric W

##### MrExcel MVP
You left out a few details about how your sheets are organized and where some values are, but you should be able to adapt this example:

Book5
ABCDEF
1Last lineDateAverageDateValue
2345Monday11.5Monday1
3Tuesday2
4Last line - 200Wednesday3
5145Thursday4
6Friday5
7Saturday6
8Sunday7
9Monday8
10Tuesday9
11Wednesday10
12Thursday11
13Friday12
14Saturday13
15Sunday14
16Monday15
MAIN
Cell Formulas
RangeFormula
C2C2=AVERAGEIF(E2:INDEX(E:E,A5),B2,F2:INDEX(F:F,A5))

#### BascherPA

##### New Member
Hello Eric,
Unfortunately I have an issue with the the code.
=AVERAGEIF(E2:INDEX(E:E,A5),B2,F2:INDEX(F:F,A5))

E2:Index(E:E,A5) should be for my code F2:Index(Gold!F:F,S2)
in S2 the last row is listed but unfortunately that doesn´t work.
F2:Index(F:F,A5) should be for my code Gold!G2:Index(Gold!G:G,S2)

Where could be the mistake?
Best regards
Benjamin

#### BascherPA

##### New Member
Sorry my mistake. For my case the correct code is
VBA Code:
``=Averageif(Gold!F2:INDEX(Gold!F:F;Gold!S2);A12;Gold!G2:INDEX(Gold!G:G;Gold!S2))``

It works. Thanks a lot.

Best regards
Benjamin

#### Eric W

##### MrExcel MVP
Glad you got it sussed out. Thanks for the feedback.

#### BascherPA

##### New Member
Hi all,
just another topic concerning this table.
I want to search again with the weekday in column F. E.g all "Mondays" in this column. After finding count the amount of cells which are > than 0 for the last 30 days. Last row in S2 and -30 days in S4.
I tried to do that with the order countif but unfortunately without any success because could search for the weekday and count the values >0 in one code.

Best regards
Benjamin

Replies
5
Views
229
Replies
2
Views
41
Replies
3
Views
167
Replies
0
Views
48
Replies
0
Views
90

1,127,449
Messages
5,624,827
Members
416,057
Latest member
VARSHA V VASWANI

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