Lookup/SUM formula

[James2908]

Hi,

I am trying to create a formula (And failing). What I am attempting to do is have a formula look up specific text i.e Spanish and then the week number and then sum up all the numbers across the date range from that week for the Spanish to give a total.

Currently I am just using a sum formula but I have to keep amending this each week.

From the images below the actual column is were I want my total to go and image 2 is the data source.

Please ignore spill error this will be corrected (My excel knowledge is improving slowly but surely!)

Hope this makes sense.

Thanks in advance.

 

[Toadstool]

Hi James2908,

Your "Telephony Data" sheet doesn't actually show what's in column V, the target of your SUM, so I'm going to make a huge assumption. My assumption is that you want to sum the numbers under each Forecast title for the week number specified.

We'll need the week number and it looks like it's part of a string so I've isolated it in D2 of the first sheet.

It is preferred you use XL2BB to post worksheet examples so others don't have to retype your example data. In my case I've just created a small subset of your data but it should extrapolate to your larger data set.

OFFSET is used to return a range of cells. In this example for Spanish and week 21 we want to SUM cells C4 to C7 (assuming the same week numbers are always adjacent). The format of OFFSET is:
OFFSET(starting cell,rows to offset, columns to offset, height in rows, width in columns)
Our starting cell is fixed at $A$2.
The first MATCH looks down column A for the first matching week number, so that's the row offset.
The second MATCH looks for Spanish across cells C2 to U2 and that becomes our column offset.
The COUNTIF counts how many week numbers are 21 so that will be the height of the range.
We are only looking to SUM one column so that parameter is not specified and defaults to 1.

The OFFSET therefore gives OFFSET($A$2,2,2,4) so it returns the range C4:C7 which the SUM then totals.

Here's my Telephony Data

Book3
	A	B	C	D	E	F
1
2	Week Number	Date	Spanish	Russian	Polish
3	20		0	1	3
4	21		10	3	4
5	21		1	2	2
6	21		0	4	1
7	21		8	8	1
8	22		1	9	7
9	23		9	1	7
10
Telephony Data

Here's my Sheet1 sample

Book3
	A	B	C	D	E
1
2		Week Number		21
3
4		Skillset	Forecast	Actual
5		Spanish		19
6		Russian		17
7		Polish		8
8
Sheet1

Cell Formulas
Range	Formula
D5:D7	D5	=SUM(OFFSET('Telephony Data'!$A$2,MATCH($D$2,'Telephony Data'!$A$3:$A$9999,0),MATCH(B5,'Telephony Data'!$C$2:$U$2,0)+1,COUNTIF('Telephony Data'!$A$3:$A$9999,$D$2)))

 

[Fluff]

Please ignore spill error this will be corrected

As you are obviously now using 365, you might want to update your profile.

You can use sumifs for what you are trying to do.

 

[James2908]

Hi James2908,

Your "Telephony Data" sheet doesn't actually show what's in column V, the target of your SUM, so I'm going to make a huge assumption. My assumption is that you want to sum the numbers under each Forecast title for the week number specified.

We'll need the week number and it looks like it's part of a string so I've isolated it in D2 of the first sheet.

It is preferred you use XL2BB to post worksheet examples so others don't have to retype your example data. In my case I've just created a small subset of your data but it should extrapolate to your larger data set.

OFFSET is used to return a range of cells. In this example for Spanish and week 21 we want to SUM cells C4 to C7 (assuming the same week numbers are always adjacent). The format of OFFSET is:
OFFSET(starting cell,rows to offset, columns to offset, height in rows, width in columns)
Our starting cell is fixed at $A$2.
The first MATCH looks down column A for the first matching week number, so that's the row offset.
The second MATCH looks for Spanish across cells C2 to U2 and that becomes our column offset.
The COUNTIF counts how many week numbers are 21 so that will be the height of the range.
We are only looking to SUM one column so that parameter is not specified and defaults to 1.

The OFFSET therefore gives OFFSET($A$2,2,2,4) so it returns the range C4:C7 which the SUM then totals.

Here's my Telephony Data

Book3
	A	B	C	D	E	F
1
2	Week Number	Date	Spanish	Russian	Polish
3	20		0	1	3
4	21		10	3	4
5	21		1	2	2
6	21		0	4	1
7	21		8	8	1
8	22		1	9	7
9	23		9	1	7
10
Telephony Data

Here's my Sheet1 sample

Book3
	A	B	C	D	E
1
2		Week Number		21
3
4		Skillset	Forecast	Actual
5		Spanish		19
6		Russian		17
7		Polish		8
8
Sheet1

Cell Formulas
Range	Formula
D5:D7	D5	=SUM(OFFSET('Telephony Data'!$A$2,MATCH($D$2,'Telephony Data'!$A$3:$A$9999,0),MATCH(B5,'Telephony Data'!$C$2:$U$2,0)+1,COUNTIF('Telephony Data'!$A$3:$A$9999,$D$2)))

That is absolutely magical thank you

This has worked perfect, Thank you so much for your help!

 

[Toadstool]

That is absolutely magical thank you

This has worked perfect, Thank you so much for your help!

You're welcome!
Thanks for the feedback.