Excel Question

T

twiggles

Active member

Feb 14, 2014

115

• Jul 25, 2016

• #1

Hoping someone will be able to help!

In column A and D I have duplicate customer names (Column D has more than A). In Column B I have 2015 Sales Volume and in E I have 2016 Sales Volume.

In column G I want to have a sales variance.

Looking for a formula that will match the customer name then minus the value of Column B against E.

Hope I explained that ok!

Any help would be greatly appreciated.

Cheers

 

A

akipling

Active member

Jan 12, 2010

163

Morecambe

• Jul 25, 2016

• #2

=sumif(a:a,d1,b:b)-sumif(a:a,d1,e:e)

Enter in cell G1 then copy down

 

T

twiggles

Active member

Feb 14, 2014

115

• Jul 25, 2016

• Thread starter
• #3

Thanks for this. Does it only work if the same customer name is in the same row? As I have extra customers in column D they are not in matching order all the way down

 

A

akipling

Active member

Jan 12, 2010

163

Morecambe

• Jul 25, 2016

• #4

Yes unfortunately where there is no matching customer it will return zero.

Alternatively you could use....

=e1-vlookup(d1,a:b,2,false)

This will return #N/A where there is no previous years info. However if a customer appears more than once in the previous year it will only include the first occurrence in the calculation

 

T

twiggles

Active member

Feb 14, 2014

115

• Jul 25, 2016

• Thread starter
• #5

akipling said:

Yes unfortunately where there is no matching customer it will return zero.

Alternatively you could use....

=e1-vlookup(d1,a:b,2,false)

This will return #N/A where there is no previous years info. However if a customer appears more than once in the previous year it will only include the first occurrence in the calculation

Click to expand...

It worked perfectly.

Thank you very much!

 

Tom Hark Preston Park

Will Post For Cash

Jul 6, 2003

70,124

• Jul 25, 2016

• #6

twiggles said:

It worked perfectly.

Thank you very much!

Click to expand...

Further proof, should any be needed, that NSC can be a force for good, as well as evil.

 

M

MattBackHome

Well-known member

Jul 7, 2003

11,719

• Jul 25, 2016

• #7

Tom Hark said:

Further proof, should any be needed, that NSC can be a force for good, as well as evil.

Click to expand...

Agreed - I only come back for the Excel questions these days.