Vlookup

[addbrad]

Hi,

Hopefully somebody can help me with this as it is puzzling me.

I am trying to auto add the total annual sales for a group of products as follows:

Tab "Customer Analysis" - cell F8 = total annual turnover of a group of products, I have one tab with a product group (Data) and one tab with all products & their annual turnover (2017).

The products group are in Tab "Data" B8:B14, Tab "2017" contains all products turnover annually, I only want to work out the total annual spend for products B8:B14 for cell F8. In Tab "2017" A3:A40 is the product code & N3:N40 is the annual turnover of the product code.

Does that make sense ??

For example:

B8:B14 on tab "Data" contains something like:

1111
2222
3333
4444
5555
6666
7777

A3:A40 on tab "2017" contains the above codes plus 8888, 9999 and so on

N3:N40 on tab "2017" contains the annual spend for A3:A40 and so on

A3:A40 N3:N40

1111 £1111
2222 £2222
3333 £3333
4444 £4444
5555 £5555
6666 £6666
7777 £7777
8888 £8888
9999 £9999

As we only only want to add the line in bold the total annual turnover that should be displayed in F8 on tab "Customer Analysis" is £311808 (1111+2222+3333+4444+5555+6666+7777).

Thanks

 

[Eric W]

Re: Excel & Vlookup Help

Welcome to the forum.


If I understand correctly, try:

=SUM(SUMIF('2017'!A:A,Data!B8:B14,'2017'!B:B))

confirmed with Control+Shift+Enter.

 

[addbrad]

Re: Excel & Vlookup Help

Welcome to the forum.


If I understand correctly, try:

=SUM(SUMIF('2017'!A:A,Data!B8:B14,'2017'!B:B))

confirmed with Control+Shift+Enter.

Hi Eric, thanks for the reply. This returns £0 unfortunately

A couple of extra points, "2017" rows 1 & 2 contains title and total spend and so this needs to not be used in the calculation.

I think Column N should be used in tab "2017" ??

 

[Eric W]

Re: Excel & Vlookup Help

The heading shouldn't matter, but you're right, I missed column N, try this:

=SUM(SUMIF('2017'!A:A,Data!B8:B14,'2017'!N:N))

with Control+Shift+Enter.

 

[addbrad]

Re: Excel & Vlookup Help

The heading shouldn't matter, but you're right, I missed column N, try this:

=SUM(SUMIF('2017'!A:A,Data!B8:B14,'2017'!N:N))

with Control+Shift+Enter.

Perfect, so far so good

Thank You

 

[Eric W]

Re: Excel & Vlookup Help

You're welcome.