Getting sum total based on date input

gd6noob

New Member
Joined
Oct 20, 2017
Messages
35
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 motto
Not everything I do at work revolves around Excel. Only the fun parts.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,207
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
35
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,000
Messages
5,411,753
Members
403,394
Latest member
Mohan Kumar

This Week's Hot Topics

Top