sumproduct using a range of conditions as opposed to one condition

[doug firr]

Hello

I need to sum the values in a range where that range is from april - august.

The months are numbered: jan=1, feb=2, march=3 etc.

I had a formula along the lines of:

=SUMPRODUCT((B5:B8503=2011)*(C5:C8503=4:8)*(H5:H8503))

This did not work.

Note the ideal "4:8" for months april - august. The intention being where column c contains numbers 4,5,6,7 and 8 then treat as true.

Is there a way to do this simply and elegantly - sumproduct for where a range of conditions are true?

 

[Firefly2012]

Hi Doug

One way:

=SUMPRODUCT((B5:B8503=2011)+0,(C5:C8503>=4)+0,(C5:C8503<=8)+0,H5:H8503)

 

[T. Valko]

Hello

I need to sum the values in a range where that range is from april - august.

The months are numbered: jan=1, feb=2, march=3 etc.

I had a formula along the lines of:

=SUMPRODUCT((B5:B8503=2011)*(C5:C8503=4:8)*(H5:H8503))

This did not work.

Note the ideal "4:8" for months april - august. The intention being where column c contains numbers 4,5,6,7 and 8 then treat as true.

Is there a way to do this simply and elegantly - sumproduct for where a range of conditions are true?

One way...

Use cells to hold the criteria:

A1 = 2011
B1 = 4
C1 = 8

=SUMPRODUCT(--(B5:B8503=A1),--(C5:C8503>=B1),--(C5:C8503<=C1),H5:H8503)

 

[T. Valko]

One way...

Use cells to hold the criteria:

A1 = 2011
B1 = 4
C1 = 8

=SUMPRODUCT(--(B5:B8503=A1),--(C5:C8503>=B1),--(C5:C8503<=C1),H5:H8503)

If you're using Excel 2007 or later use the SUMIFS function:

=SUMIFS(H5:H8503,B5:B8503,A1,C5:C8503,">="&B1,C5:C8503,"<="&C1)

 

[doug firr]

@firefly2012 that worked thanks a lot. Out of curiosity what does "+0" do between the brackets in the function? Never seen that before. Either way I followed your logic using "*" and that has worked and saved me time - many thanks! If I knew of a way to mark question as resolved I would. @Biff thanks too I just tried firefly's method 1st

 

[T. Valko]

@Biff thanks too I just tried firefly's method 1st

No problem!

 

[Firefly2012]

@firefly2012 that worked thanks a lot. Out of curiosity what does "+0" do between the brackets in the function? Never seen that before. Either way I followed your logic using "*" and that has worked and saved me time - many thanks! If I knew of a way to mark question as resolved I would. @Biff thanks too I just tried firefly's method 1st

Each conditional argument in the Sumproduct e.g:

(A1:A10=SomeValue)

returns an array (list) of True/False values dependent on whether the values in A1:A10 equal SomeValue.

Unfortunately, Sumproduct doesn't know how to treat lists of True/Falses without some additional help to convert them to 1s and 0s.

One such way of doing this is to add 0 (+0) on the basis that

True + 0 = 1
False + 0 = 0

Other ways you may see of achieveing this are -- and *1. Any mathematical operation will coerce the True/Falses to 1s and 0s.

This is why in your original it works by you mulitplying the conditional arrays together as True * True = 1 and and True/False * False = 0.


Don't worry about marking it solved - this forum doesn't support that facility

 

[T. Valko]

(Almost) everything you always wanted to know about SUMPRODUCT (but were afraid to ask):

http://xldynamic.com/source/xld.SUMPRODUCT.html