Getting sum total based on date input

gd6noob

New Member
Joined
Oct 20, 2017
Messages
30
Hi,

Im trying to get the sum total based on the Date input at D3 here is my formula =SUM(INDIRECT("Data!"&ADDRESS(MATCH($C5,Data!B:B,0),MATCH($D$3,Data!$1:$1,0))&":"&ADDRESS(MATCH($C6,Data!B:B,0)-1,MATCH($D$3,Data!$1:$1,0))))

The problem Im facing, in this example, the Data tab is auto pulled from the web and my formula only works if the Date remains on row 1 and the sports remain in column b.
On my actual workbook, the site Im pulling data from, does not allow me to select which part but the whole page, like I normally can with most other sites. The actual data wont fall under these conditions and will in different rows and columns.
Is there another way to do this?



 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,204
Office Version
365
Platform
Windows
Hi Gd6noob,
If your data is at a random spot in the sheet, you probably first need a function (or two) to find out where the data starts. You could use a couple of MATCH formulae to e.g. find "Hockey" if that's a good indicator of where your data starts. You could then use that found row/column to build your formula. By the way: the OFFSET formula also allows you to select a region (width & heigth are two parameters).
Generally speaking I would try to build some VBA to pull in the data on a fixed location, that makes life so much easier :).
Hope those pointers help,
Koen
 

gd6noob

New Member
Joined
Oct 20, 2017
Messages
30
Hi Rijnsent,

The data in the Data tab doesnt always come to a random spot, its in a fixed location further down, row 396, but my formula, for some reason needs to be in this row/column. Ive tried to manipulate it but returns a #N/A error.
 

Forum statistics

Threads
1,089,223
Messages
5,406,948
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top