SUM/Lookup database

[Bravo2003]

This is probably very simple, but I can't think of the right solution at present.

Spreadsheet


On a seperate Sheet, i need to add a formula that will look across row 1 for xDate, then row 2 to find Header Title then find row related to Totals in Column A and result with the answer to that column/Row intersection.

Example- Find Total Number for Feb 19 = 7 (G6)

To Note, total row will move up/down depending on imported data,

Date and headers can be based on a lookup option, i.e the headers/dates will be included as headers in the new results sheet. But not included in the same repeat layout!

Any tips?

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1							Feb-19
2							Number
3
4
5
6
7
8	Total						7
9
10
11	Feb-19	Number	Total	7
Sheet2

Cell Formulas
Range	Formula
D11	=SUMPRODUCT(--($A$1:$A$8=$C11)*--($A$1:$L$1=$A11)*--($A$2:$L$2=$B11),$A$1:$L$8)

 

[Bravo2003]

try this

	A	B	C	D	E	F	G	H	I	J	K	L
1							Feb-19
2							Number
3
4
5
6
7
8	Total						7
9
10
11	Feb-19	Number	Total	7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D11 =SUMPRODUCT(--($A$1:$A$8=$C11)*--($A$1:$L$1=$A11)*--($A$2:$L$2=$B11),$A$1:$L$8) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks, Ive got this to work using this adjusted formula

=SUMPRODUCT(--('2018 Sales'!$B1:$B600="Overall Result")*--('2018 Sales'!$B$16:$BY$16="January 2018")*--('2018 Sales'!$B$17:$BY$17=$BK$60),'2018 Sales'!$B1:$BY600)

But because the date headers on each sheet don't format the same (master data is imported in January 2018 format, but results sheet won't format the same and is Jan-18

Anyway to adjust so I can just copy paste the formula along for different months/years and not have to over write the date in formula?

 

[AlanY]

different format of dates should work, see below.
may be some dates are stored as text

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1							January 2018
2							Number
3
4
5
6
7
8	Total						7
9
10
11	Jan-18	Number	Total	7
Sheet1

Cell Formulas
Range	Formula
D11	=SUMPRODUCT(--($A$1:$A$8=$C11)*--($A$1:$L$1=$A11)*--($A$2:$L$2=$B11),$A$1:$L$8)

 

[AlanY]

Thanks, Ive got this to work using this adjusted formula

=SUMPRODUCT(--('2018 Sales'!$B1:$B600="Overall Result")*--('2018 Sales'!$B$16:$BY$16="January 2018")*--('2018 Sales'!$B$17:$BY$17=$BK$60),'2018 Sales'!$B1:$BY600)

But because the date headers on each sheet don't format the same (master data is imported in January 2018 format, but results sheet won't format the same and is Jan-18

Anyway to adjust so I can just copy paste the formula along for different months/years and not have to over write the date in formula?

try change the "January 2018" to

=DATEVALUE(TEXT("January 2018","MMM-YY"))

 

[Bravo2003]

try change the "January 2018" to

=DATEVALUE(TEXT("January 2018","MMM-YY"))

Yes, i think it's the imported data January 2018 is set as text. unfortunately I can't amend the format for this as it's imported and updated at least once a day and I don't want to have to keep having to set the format for the imported data everytime.