0

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.

0

The formula for the increases is

=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))
  • That's it!! Thank you! I thought SumProduct might be the one I wanted, but I just couldn't figure it out. Thanks! – OzzyKP Jan 14 at 14:16
  • Oh, actually, one more question. If I wanted to count how many salespeople gained and how many declined. How would I do that? – OzzyKP Jan 14 at 14:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.