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

#### schreibman

##### New Member
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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?

if:
HTML:
``````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:

HTML:
``````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)

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

Thanks for the feedback.

Replies
5
Views
132
Replies
2
Views
413
Replies
6
Views
273
Replies
9
Views
217
Replies
1
Views
241

1,207,199
Messages
6,077,021
Members
446,251
Latest member
dpf220

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back