Is there a SUMIF equivilant that is a 'lessif'?

[schreibman]

I have a feeling there are many easy solutions, and I am just having a mental block, so thanks in advance if I am missing the obvious here.

Consider the following, stats created monthly for a sales team.
Also, sales folks are added and leave monthly.

How can I calculate the prior month difference, but only if the name is in the prior month and current month?

Like a SUMIF, but a 'Minus if'...

The last table is what I am trying to find a function to calculate:

WidgetsSold
1/1/2012 Smith 10
1/1/2012 Johnson 8
1/1/2012 jones 11
1/1/2012 jackson 12


2/1/2012 Smith 10
2/1/2012 Stark 7
2/1/2012 jones 12
2/1/2012 jackson 11


Calculate this one:

Dif Smith 0
Dif jones 1
Dif jackson -1

 

[cyrilbrd]

Hi,

are all data in the same sheet? month n and month n+1?
what about occurrences of name that do have only one entry?

 

[cyrilbrd]

if:

WidgetsSold		
1/1/12	Smith	10
1/1/12	Johnson	8
1/1/12	jones	11
1/1/12	jackson	12
		
		
2/1/12	Smith	10
2/1/12	Stark	7
2/1/12	jones	12
2/1/12	jackson	11

then:

Month1	1/1/12
Month2	2/1/12
Smith	0
Johnson	-8
jones	1
jackson	-1
Stark	7

formula as follows:
=SUMIFS($C$2:$C$11,$A$2:$A$11,$B$17,$B$2:$B$11,A18)-SUMIFS($C$2:$C$11,$A$2:$A$11,$B$16,$B$2:$B$11,A18)

 

[schreibman]

Thanks Soooo much! I could NOT think of that! Perfect! Excellent!!!

 

[cyrilbrd]

Glad it worked for you.

Thanks for the feedback.