# SUMPRODUCT help

D

#### DoooWhat

I need to find the difference between the maximum and minimum values
of several different groups. I have the following data:

A B
1 1/1/2008 2008
2 1/15/2008 2008
3 1/31/2008 2008
4 4/1/2009 2009
5 4/21/2009 2009
6 7/1/2010 2010
7 7/14/2010 2010
8 7/26/2010 2010

I want to know what the range of dates is for each year. For example,
in analyzing 2008, I want a formula that will produce a value of 30.
For 2009, it should be 20. For 2010, it should be 25.

My list will be very large and will not necessarily be in the right
order. I tried to use a combination of "min", "max", and
"sumproduct", but I couldn't get it to work. This was what I came up
with.

=SUMPRODUCT((A1:A500>=MIN(A1:A500))*(A1:A500<=MAX(A1:A500))*(B1:B500="2008"))

Any help would be very much appreciated.

Kevin

T

#### T. Valko

One way:

Try this array formula** :

D1 = year number

=MAX(IF(YEAR(A\$1:A\$500)=D1,A\$1:A\$500))-MIN(IF(YEAR(A\$1:A\$500)=D1,A\$1:A\$500))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

C

#### Conan Kelly

DoooWhat,

Using your example, I would put a list of all possible years below the data
(you could put it along side or on a different sheet if you want). So in
cells B10:B12, I would have 2008, 2009, & 2010.

Then in A10, I would enter this formula:

=sumproduct((max(\$A\$1:\$A\$8))*(\$B\$1:\$B\$8=\$B10))-sumproduct((min(\$A\$1:\$A\$8))*(\$B\$1:\$B\$8=\$B10))

Then copy that formula down to A11 & A12. Format A10:A12 as a number
instead of a date.

(I did not test this formula...I'm taking an educated guess and I hope my
syntax is correct)

HTH,

Conan

B

#### Bernard Liengme

I just used 8 cells to test (need not see the need for column B)
This array formula (use CTRL+SHIFT+ENTER to compete it)
=MAX(IF(YEAR(A1:A8)=2008,A1:A8)) gives the MAX date when year is 2008
Similarly =MIN(IF(YEAR(A1:A8)=2008,A1:A8)) will give MIN value
Subtract the two for range
Or combine the two as
=MAX(IF(YEAR(A1:A8)=2008,A1:A8)) - MIN(IF(YEAR(A1:A8)=2008,A1:A8))
to get answer in one step
If you get #VALUE! you forgot CTRL+SHIFT+ENTER

With 2008, 2009 etc in B1....use
=MAX(IF(YEAR(\$A\$1:\$A\$8)=B1,\$A\$1:\$A\$8)) -
MIN(IF(YEAR(\$A\$1:\$A\$8)=B1,\$A\$1:\$A\$8))
copy down to do all years

C

#### Conan Kelly

I decided to work through it. Looks like this will work:

=SUMPRODUCT((MAX((\$A\$1:\$A\$8)*(\$B\$1:\$B\$8=\$B10))))-SUMPRODUCT(SMALL((\$A\$1:\$A\$8)*(\$B\$1:\$B\$8=\$B10),COUNTIF(\$B\$1:\$B\$8,"<>"&\$B10)+1))

But a couple others came up with others that will probably work.

HTH,

Conan

D

#### DoooWhat

Thanks so much Conan! That worked perfectly.

Kevin