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

BascherPA

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
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
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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
 

BascherPA

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,354
Messages
5,601,139
Members
414,430
Latest member
jtdinh205

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
Top