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

[BascherPA]

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.

Thank you in advance for your support.

Best regards
Benjamin

 

[Eric W]

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
	A	B	C	D	E	F
1	Last line	Date	Average		Date	Value
2	345	Monday	11.5		Monday	1
3					Tuesday	2
4	Last line - 200				Wednesday	3
5	145				Thursday	4
6					Friday	5
7					Saturday	6
8					Sunday	7
9					Monday	8
10					Tuesday	9
11					Wednesday	10
12					Thursday	11
13					Friday	12
14					Saturday	13
15					Sunday	14
16					Monday	15
MAIN

Cell Formulas
Range	Formula
C2	C2	=AVERAGEIF(E2:INDEX(E:E,A5),B2,F2:INDEX(F:F,A5))

 

[BascherPA]

Hello Eric,
Thank you in advance for your reply.
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]

Sorry my mistake. For my case the correct code is

=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]

Glad you got it sussed out. Thanks for the feedback.

 

[BascherPA]

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