# How do I perform a calculation in rows that meet several conditions?

I have two columns of numbers: a 2017 column and a 2018 column. Some cells are blank, the others have numbers.  (Or some cells contain zero and some cells contain positive numbers.)

I want to know the difference between 2018 and 2017, but only in cases (rows) where neither column is blank and only in cases where the 2018 number is larger than 2017. Then I want to add them all together for a grand total.

An example: I have a number of salesmen (each row). Some of them had sales in both 2017 and 2018, and some of them just started last year. I want a grand total of the increase in sales among salesmen who improved their sales from 2017 to 2018, and not count salesmen who just started in 2018 (i.e., blank cells in 2017).

As a separate figure but analogous, I want a grand total of the decrease in sales among all salesmen whose sales dropped from 2017 to 2018 who were active both years.

I've looked into `SUMIFS` and `IF` and `SUMPRODUCT` and arrays, but I can't figure it out.

Here are some sample numbers and desired results:

``````**Salesman**----**2017 sales**---**2018 sales**
Todd Packer----------\$14,547---------\$15,487
Michael Scott--------\$26,487---------\$24,491
Dwight Schrutte------\$19,124---------\$19,458
Phyllis Vance--------\$13,891---------\$14,551
Stanley Hudson-------\$17,541---------\$17,541
Jim Halpert----------\$15,874---------\$15,732
Andy Bernard--------------\$0----------\$9,574
Pam Beasley-----------\$2,123--------------\$0
``````

Ok, 8 salespeople. Todd, Dwight, and Phyllis increased their sales from 2017 to 2018. Michael & Jim saw their sales decline. Stanley stayed the same. Andy had no sales in 2017, but had some in 2018. Pam had sales in 2017, but no sales in 2018.

In 2017 Todd, Dwight and Phyllis had \$47,562 in sales. In 2018 they had \$49,496 in sales. They increased their sales by \$1,934. That's the number I want.

In 2017 Michael & Jim had \$42,361 in sales. In 2018 they had \$40,223 in sales. They decreased their sales by \$2,138. That's the number I want.

For the purposes of this calculation I want to ignore Stanley, Andy & Pam.

I have a separate sheet where I am putting various totals and summary figures to slice, dice and summarize the raw figures. I don't want to add another column to the raw figures sheet, I just want one cell to contain a total for salesmen who increased and one for those who decreased.

``````=SUMPRODUCT(C2:C9-B2:B9, --(C2:C9>B2:B9), --(B2:B9>0))
To get the decreases, just exchange `B` and `C`:
``````=SUMPRODUCT(B2:B9-C2:C9, --(B2:B9>C2:C9), --(C2:C9>0))